Changing Link/Hyperlink filenames in Excel VBA

We're changing file servers in a couple of weeks, so our share names are going to change. We'll be keeping the logical name (J:), but will be changing from something like:
\\filesrv1\ABC$
to:
\\filesrv2\ABC$

Subsequently, I have to help people sort out their links in Excel (and other Office apps), and update those to the new file server name.

I ran a test, and using the same drive letter assigned to the new share does not do the trick - I get an error "Cannot start the source application for this object" for Links, and "The Address of this site is not valid. Check the address and try again." for hyperlinks.

I've done some research, and the Excel VBA .LinkSources Method only reveals the drive letter. I need a method that will allow me to address (and change) the full file path (\\filesrv1\abc$\...).

Using the .SourceName Method on an OLEObject also only reveals a string of the sort:
"MSPhotoEd.3|J:\pictest\testpicture.JPG!"'

On the Hyperlink side, the .Hyperlinks method only exposes "../../../abc$/pictest/testpic.jpg". This is a problem...

I know that PPT has methods (for Links and Hyperlinks) that will expose the fully qualifed filename, so I'm able to cover that application. I need that same functionality in Excel, or we're in BIG trouble with our users when we make the cutover.

Any ideas?
thanks in advance for any help you can provide...Start Free Trial