Ruined hyperlinks...

[longtalker]

Hello everybody!

I have a spreadsheet that contains details about various TXT files: the first column contains the files' names, and the following columns describe various parameters of the files. Every cell in the first column is also a hyperlink, to that particular file. The localisation of the files on the hard-drive is such that each one is in its own subfolder in D:\Data\.

When I opened the spreadsheet today, I noticed that none of the links were working, and when I checked the hyperlinks, they had all changed from D:\Data\Subfolder\File.txt into C:\Documents and Settings\Administrator\Application Data\Microsoft\Excel\Subfolder\file.txt

Can anyone suggest (1) why this might have happened all by itself (or what I might have done that had this as an effect), and (2) how I can fix the links (other than by manually correcting each hyperlink.. there are about 500 rows in the spreadsheet!)

Thank you very much in advance for any help!

Catalin

[6StringJazzer]

I had something like this happen once. Never figured out what caused it. I used VBA to fix it.

Please Login or Register  to view this content.

I have tested this. [Apparently not well enough! I had an error in how to iterate through the links. I believe it is now correct.]

HOWEVER, I still need to know how to construct the subfolder name for this to work for you.

An alternative would be to add an extra column to your worksheet and use the HYPERLINK function in that column to build the link from the file name given in your current column A. This adds an extra column and puts the link in a separate column from the file name, but

[longtalker]

Hi Jazzer! Thanks for your reply

I realised that an easy way to fix this would be to do a simple Find&Replace, since the subfolder name was fortunately maintained in the new ("ruined") link. This way, I'd just have to replace all occurences of "C:\Documents and Settings\Administrator\Application Data\Microsoft\Excel\" with "D:\Data\".

BUT, I'd have to perform this Find&Replace operation not in the regular spreadsheet but somewhere where the contents of the hyperlinks are displayed as plain text (or as some sort of markup). This is where my knowledge of Excel stops, because I have no idea where I might see my spreadhseet in such a form!

[6StringJazzer]

Catalin,

Unfortunately Excel does not have a built-in way to do Find & Replace on hyperlink addresses. There are add-ins that do things like that, but for a one-time fix I suggest you go with VBA. The following code has been modified to do what you described. If you are not comfortable with VBA I would be more than happy to go ahead and do this for you, if you attach your workbook.

Please Login or Register  to view this content.

[longtalker]

It worked, many thanks for this!

[teylyn]

Longtalker, Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read the link in my signature about cross-posting and how to do it politely. Then, check the forum rules before you post here again.

regards