Need technical help with my Excel UrS library re: Hyperlinks to pdf's

[UrS4boy]

While I am unable to work on my UrS4s, I have the time to get back to my "library" of UrS (RR UrQ, S2, UrS4, UrS6 and RS2) magazine articles. I am using Excel to keep them organized and accessible to me. I have cataloged 200 articles, 50 related (C4 100/A6, etc) articles and 34 "brochures". I have a bunch of "new" ones that have yet to be pdf'd, filed and cataloged.

Once cataloged, I link the article title to the pdf using a hyperlink. Worked beautifully until one day, Excel did something (still don't know what) and all the hyperlinks were corrupted. The titles and file folder names were still good but the corruption created invalid pathways to the file folders. Someone helped me a processed the 200 article hyperlinks with a macro to correct the pathway. Unfortunately, at the time that was to a "K" drive on my old computer which died in the early summer. Now those files are located on an "F" drive (same Transcend 16 GB thumb drive).

Initially, the original Excel spreadsheet and the files were all on the same drive and I assumed that even if they moved together, the link would still (somehow) work. Naive or clueless, your choice.

Before I pdf the new 30 or so articles and catalog them, I am hoping for hints as to how to prevent this (the hyperlink corruption) from happening again. I can fix things now (once) but I can't keep fixing them over and over again.

Thanks in advance for your hints.

[Nuvo]

pm me a link to the file and I'll take a look if you like.

[rxl]

change the drive letter http://www.howto-connect.com/windows...sk-management/

[UrS4boy]

pm me a link to the file and I'll take a look if you like.

Thanks. But I've been down that path before - file was magically fixed by a volunteer but I didn't learn anything and now it's broken again. Needs to be something that I can do myself. But thanks for the offer.

[UrS4boy]

change the drive letter http://www.howto-connect.com/windows...sk-management/

Running Windows 8 and no plans to go to 10. 8 doesn't seem to have the same feature to rename the drive.

[rxl]

All windows systems do have disk management system so just Google how to find it in your windows system

Gesendet von meinem GT-I9505 mit Tapatalk

[Henkjan]

don't hard-link to a drive-folder, but use relative to the folder in which the excel file is stored, so in case the pdf's are stored in a subfolder under the folder where the excel is stored, the link would be .\pdf\nameofpdf.pdf, if it is one folder up use ..\nameofpdf.pdf, etc

[mcandmar]

As above, once you have a relative file path working use find and replace to change the rest of them.

I also should point out a thumb drive is the worst possible storage medium you could use for file storage, they are for temporary transport of data only and should never be relied on long term. Please make multiple copies, keep the files on your computer and make sure you have another copy somewhere else as a backup.

[UrS4boy]

don't hard-link to a drive-folder, but use relative to the folder in which the excel file is stored, so in case the pdf's are stored in a subfolder under the folder where the excel is stored, the link would be .\pdf\nameofpdf.pdf, if it is one folder up use ..\nameofpdf.pdf, etc

That is exactly how I originally set it up. And it was working fine until Microsoft VISTA did something nasty and Fk'd up the pathways.

As above, once you have a relative file path working use find and replace to change the rest of them. I also should point out a thumb drive is the worst possible storage medium you could use for file storage, they are for temporary transport of data only and should never be relied on long term. Please make multiple copies, keep the files on your computer and make sure you have another copy somewhere else as a backup.

Not sure if find and replace will work with the hidden hyperlinks (I guess I will have to try).

Didn't know that about thumb drives. I used it to take all my personal files off my company laptop when I retired in June 2010. It has been fine. I will port things over to the new desktop that I am using now and back it up to a 1 Tb solid state drive that I used to back up my previous computer just before its hard drive died.

[UrS4boy]

Not sure if find and replace will work with the hidden hyperlinks (I guess I will have to try).

I tried find and replace on the unexposed hyperlinks and it didn't work. Not sure if there is another way to make it work or not.

[mcandmar]

Hmm, that does seem to be an issue, damn hyperlinks. I did a quick google search and it seems there is no quick and easy way to get at them without writing macros as the find function can only see the friendly text. For the time it would take to write a macro and tease out the bugs it would be easier to just manually update the links.

[macspring]

tbh, Dave your stuff is so valuable to us on here and quattroworld and elsewhere, it should be on your laptop, two standalone shortage devices, and the cloud, as well as a third standalone storage device that is not kept in your house.

Just sayin..............as I find your links, pdfs etc. a godsend.

If you set up a paypal account, I'd happily contribute to the cost of the storage devices. such is the 'value' of the information and the imperative in preserving it in my mind.

[UrS4boy]

Hmm, that does seem to be an issue, damn hyperlinks. I did a quick google search and it seems there is no quick and easy way to get at them without writing macros as the find function can only see the friendly text. For the time it would take to write a macro and tease out the bugs it would be easier to just manually update the links.

I have moved all the source files from my thumbdrive to a new folder on my desktop harddrive. Then I saved the more recent version of the master Excel spreadsheet in the same folder and started editing the 200 plus magazine hyperlinks. Tedious doesn't describe it. Mind numbing gets closer. But since I can't write a macro, its what I will have to do.

[macspring]

Not sure if this is relevant or helps but.......

http://uk.pcmag.com/hard-drive-reviews/7890/guide/the-10-best-external-hard-drives

You'll understand what they're saying better than me.

This that really know, feel free to assist Dave.

[Henkjan]

if you set cell formatting to text (and then F2 and enter), does it show the full hyperlink?