Renaming hyperlinks in excel

Hi guys.. here is my dilemma regarding hyperlinks in excel.

I have a folderXXX in my server and occasionally I copy this folder and rename it to something like folderYYY to start my new project using the same set of files/folder inside it. Inside the \\server\folderXXX\folder00 there is a excel file (hyperlink.xls) which contains the hyperlinks below:

\\server\folderXXX\folder00\hyperlink.xls (text to display is hyperlink.xls)
\\server\folderXXX\folder01\word.doc (text to display is word.doc)
\\server\folderXXX\folder02\excel.xls (text to display is excel.xls)
\\server\folderXXX\folder03\p.ppt (text to display is p.ppt)

Now once I have the new folderYYY, the hyperlink.xls file still uses the folderXXX and I want to rename it to folderYYY. I know I can easily use the find/replace option but that is if I display the hyperlink in full like \\server\folderXXX\folder00\hyperlink.xls. However, I display my hyperlink as only the file itself to make it cleaner.

Is there a way to rename the hyperlink by not displaying it as a full hyperlink?

Thanks in advance!

Originally Posted by Ron Coderre

I *think* you might be able to make good use of the "Hyperlink Base" property of your file.
Once you set it in the file, all hyperlinks will attempt to locate files there before searching other locations.
Example:
File.Info.Show_all_Properties
...Hyperlink Base: \\server\folderXXX\

Then you only need to enter partial hyperlink addresses...
folder00\hyperlink.xls



Is that something you can work with?

Hi Ron,

Thanks for the quick reply. The hyperlink base option didn't work for me. If I open the hyperlink.xls file under the new base folder \\server\folderYYY all hyperlinks is still looking at \\server\folderXXX. It should point to \\server\folderYYY

Any ideas?

Thanks!

Originally Posted by Ron Coderre

Did you adjust your hyperlinks so the address picks up *after* the hyperlink base location?
If you enter the entire hyperlink address, Excel will ignore the Hyperlink Base.

Example:
• File.Info.Show_all_Properties
...Hyperlink Base: \\server\folderXXX\

• Right-click on a cell...Hyperlink
...Text to display: Click Me
...Address: folder00\hyperlink.xls

Yes I did that already and I know what you mean now. So I would have to change the hyperlink base under properties everytime I duplicate the folder to the desired name which is \\server\folderYYY\ right?

I was thinking of something automatic This would do if I can't find an automated solution.

Thanks!

Originally Posted by Ron Coderre

Well...if you enter partial hyperlinks (like above) and leave the Hyperlink Base property blank, Excel will use the file path of the workbook containing the hyperlink as the Hyperlink Base.
Is that something you can exploit?

if i remove the hyperlink base then save the file all hyperlinks in the file are gone when i re-open it