Macro To Change link Path

[Dietcoke1953]

Greetings:

I have a spreadsheet that contains hyperlinks to 1200 documents on our server. Somehow, the links have changed to point of my local hard drive.

I wrote a macro to erase the first portion of the link, (C:\Documents and Settings\Ralph Barnes\Application Data\Microsoft\Excel\), leaving the rest of the link that points correctly to the document on our server (CIRs\Open CIRs\2014\CIR 1-14 Vendor Error - Greenville.pdf), but when I apply the macro to another cell, instead of keeping the rest of the link relative to the document it points to.ie CIR 2-14 ...etc. , the macro copies the document # from the cell I was in when I wrote the macro.

Is there any way to have the macro erase just a portion of a link and leave the rest as is?

LINK AS IT IS NOW: C:\Documents and Settings\Ralph Barnes\Application Data\Microsoft\Excel\CIRs\Open CIRs\2014\CIR 1-14 Vendor Error - Greenville.pdf

LINK I NEED MACRO TO PRODUCE: CIRs\Open CIRs\2014\Keep the rest of the link as it is in the cell I am in, instead of copying the cell the macro was written in.

I've attached a portion of the spreadsheet.

Thanks for any assistance you may be able to offer.

[ranman256]

Please Login or Register  to view this content.