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

Key tips and advice the working tax pro can use.
#1
Posts:
886
Joined:
26-Feb-2016 10:14pm
Location:
Oakland CA
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?
 

#2
makbo  
Posts:
6840
Joined:
23-Apr-2014 3:44pm
Location:
In The Counting House
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.
 

#3
Posts:
886
Joined:
26-Feb-2016 10:14pm
Location:
Oakland CA
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 ?
 

#4
makbo  
Posts:
6840
Joined:
23-Apr-2014 3:44pm
Location:
In The Counting House
You can select all the cells containing hyperlinks. Then right-mouse-click, and one of the choices is "Remove hyperlinks".
 

#5
Posts:
29
Joined:
4-Dec-2016 2:53pm
Location:
Wisconsin
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
 

#6
Posts:
886
Joined:
26-Feb-2016 10:14pm
Location:
Oakland CA
Will do. Thanks.
Len
 

#7
Posts:
29
Joined:
4-Dec-2016 2:53pm
Location:
Wisconsin
Sent!
 

#8
adamant  
Posts:
326
Joined:
12-Feb-2015 9:13am
Location:
Idaho
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.
 

#9
eze  
Posts:
311
Joined:
8-May-2014 7:02pm
Location:
Grey Area, California
Come geek out with me in Amsterdam....what could go wrong?

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

#10
eze  
Posts:
311
Joined:
8-May-2014 7:02pm
Location:
Grey Area, California
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????
 

#11
adamant  
Posts:
326
Joined:
12-Feb-2015 9:13am
Location:
Idaho
Hit me up when it gets closer.
 


Return to Tax Prep: Important tips and advice



Who is online

Users browsing this forum: No registered users and 13 guests