Mass editing Hyperlinks

Hello All,

I'm currently trying to mass edit hyperlinks in an excel file that links a chemical name to the chemicals' MSDS sheet in the same folder. The file was moved to a new drive so more people could have access to it, and now I need to change the hyperlinks. However, the hyperlinks all begin with the following:

../../../EHS%20Share/MSDS/Chemistry%20MSDS/

of which is followed by the chemical name. I have not be able to get any VBA macros to work and I believe it has something to do with the '../../../ at the beginning of the drive. For reference, the actual location the above is directing to is:

O:\EHS Share\MSDS\Chemistry MSDS\

And the new location I would like the hyperlinks to point to is:

O:\Chemistry Share\Chemistry MSDS\2010 Chemicals\

Any help would be greatly appreciated!
Lividtex

Originally Posted by JBeaucaire

I *think* you can get the links to update themselves by opening BOTH files, then save the "link to" file in the new location.

I was wondering if you could be a little more specific? I don't quite follow where you're going with that idea.


And oldchippy, I tried your suggestion and had the same problem. It seem that after editing the macro to contain my old file and new file I have the same problem as before that it doesn't find what I'm looking for. I set up a test with a basic hyperlink to www.google.com and had the macro supplied change all hyperlinks to www.google.com to www.yahoo.com and that works. However, the macro does not work when I copy and past the above address for each hyperlink.

Thanks guys for the help but I figured it out a different way. I'm not sure what the ../../../ was referring to, but I used a replace macro to change that to ..\..\..\ and then used another replace macro to change the entire string I needed changed. Just for future reference!