Key tips and advice the working tax pro can use.
callling all Excel guru's: how2 change absolute links to rel
Post a reply

17-Aug-2018 12:45am

How can i quickly change about 150 absolute hyper links to pdf files to relative hyperlink's?

Thought I was so smart to insert links to 150 pdf's in an Excel sheet. And thought I'd be nice and tidy by first placing the pdf's in a separate folder. Both folders on same drive (actually on OneDrive)



Now I want to move both the folders to a different OneDrive folder without breaking the links.



Will test tomorrow but I'm pretty sure this will break the links.



Since i don't know how to write vba script, anyone know of a dummy's way to convert these absolute hyper links to relative hyperlinks other than editing each one? Maybe a way to search and replace?



Next time, isn't there something i should change in FIle, properties about the "base"that will automatically force all links to be relative?

18-Aug-2018 10:06am

Last question: yes, there is an advanced property related to the base of hyperlinks, I haven't used it but it's documented in the help.

To your main problem, again I haven't had to do this, but I'd try to work with the HYPERLINK() function. Something like, remove the links so you have only the text (this can be done in a batch), then use find/replace to edit the text of each link, then use HYPERLINK function to re-create usable links.

18-Aug-2018 12:48pm

thanks. I'll try that. as it turns out something already kaboshed all the links, so i'll have to relink them regardless.

Makbo, when you say "can be done in a batch" do you mean using search and replace within Excel or ?

18-Aug-2018 2:39pm

You can select all the cells containing hyperlinks. Then right-mouse-click, and one of the choices is "Remove hyperlinks".

30-Nov-2018 4:20pm

We link pdfs in massive files on a regular basis. Typically this is used for sale and use tax audits so that the auditor can easily click on the link to get the supporting documentation.

If your still needing help, let me know and I can send instructions on how to batch link pdfs to an excel document.

-Damon

30-Nov-2018 4:28pm

Will do. Thanks.
Len

30-Nov-2018 5:13pm

Sent!

1-Feb-2019 10:33pm

For today who want to up their Excel game, you should look into ASAP utilities.

I'm probably in the realm of Excel nerd, dabbled in writing my own VBA, and this is a collection of quality utilities.

1-Mar-2019 12:24pm

Come geek out with me in Amsterdam....what could go wrong?

https://topexcelclass.com/index.php/ams ... el-summit/

1-Mar-2019 12:30pm

adamant wrote:For today who want to up their Excel game, you should look into ASAP utilities.

I'm probably in the realm of Excel nerd, dabbled in writing my own VBA, and this is a collection of quality utilities.



Wow....there's some cool features. Conditional Sum by color format????

1-Mar-2019 7:45pm

Hit me up when it gets closer.
Post a reply