Page 1 of 1

callling all Excel guru's: how2 change absolute links to rel

PostPosted: 17-Aug-2018 12:45am
by lenraphael
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?

Re: callling all Excel guru's: how2 change absolute links to

PostPosted: 18-Aug-2018 10:06am
by makbo
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.

Re: callling all Excel guru's: how2 change absolute links to

PostPosted: 18-Aug-2018 12:48pm
by lenraphael
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 ?

Re: callling all Excel guru's: how2 change absolute links to

PostPosted: 18-Aug-2018 2:39pm
by makbo
You can select all the cells containing hyperlinks. Then right-mouse-click, and one of the choices is "Remove hyperlinks".

Re: callling all Excel guru's: how2 change absolute links to

PostPosted: 30-Nov-2018 4:20pm
by usmctaxguy
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

Re: callling all Excel guru's: how2 change absolute links to

PostPosted: 30-Nov-2018 4:28pm
by lenraphael
Will do. Thanks.
Len

Re: callling all Excel guru's: how2 change absolute links to

PostPosted: 30-Nov-2018 5:13pm
by usmctaxguy
Sent!

Re: callling all Excel guru's: how2 change absolute links to

PostPosted: 1-Feb-2019 10:33pm
by adamant
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.

Re: callling all Excel guru's: how2 change absolute links to

PostPosted: 1-Mar-2019 12:24pm
by eze
Come geek out with me in Amsterdam....what could go wrong?

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

Re: callling all Excel guru's: how2 change absolute links to

PostPosted: 1-Mar-2019 12:30pm
by eze
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????

Re: callling all Excel guru's: how2 change absolute links to

PostPosted: 1-Mar-2019 7:45pm
by adamant
Hit me up when it gets closer.