If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Hypeerlinks in worksheet changed locations from server to workstat
I have a spreadsheet that contains hyperlinks to Word docs and pdfs of
reports. The spreadsheet contains database information that can direct my users to reports that were previously written on specific subjects. The hyperlinks were all set up to be directed to docs in a specific folder on server. Recently I've added to the additional references and hyperlinks to that spreadsheet. When I open the spreadsheet yesterday, all links had changed and now show the hyperlink address to my workstation hard drive, not the server. If I add new links today, they show the server as the location, but all previously established links now refer to my workstation hard drive and I cannot connect to the document because the hyperlink directs me to the wrong place. And I don't want to re-link those 300 documents. Can you help? |
#2
|
|||
|
|||
Hypeerlinks in worksheet changed locations from server to workstat
Saved from a previous post:
A few people have said that this has stopped a similar problem from occurring. Maybe it'll help you--but you'll have to test it to make sure. In xl2003 menus: File|Properties|Summary Tab|Hyperlink Base change it to C:\ (something that's always available) In xl2007: Click on the Office button Choose Prepare, then properties. Then use the arrow on the Document Properties dropdown Choose Advanced Properties And then change the hyperlink base on the Summary tab. I like to use the =hyperlink() worksheet function for this. I've never had one of these worksheet functions change. I'll put the path in a hidden cell (A1 in my example). (Include the trailing backslash, too!) Then put the filenames in A2:Axxx Then use the =hyperlink() formula in B2:Bxx. =hyperlink("File:////"&$a$1&a2,"Click me") and drag down Then when I need to change the folder, I can change it one location (A1). I could embed the path directly in the formula: =hyperlink("File:////c:\my documents\excel\"& a2,"Click me") and use Edit|Replace, but that seems like more work to me. ======== If you used Insert|Hyperlink, then you've noticed that edit|replace won't touch those hyperlink addresses. If you used Insert|hyperlink (xl2003 menus), you'll have more work to do. But the good news is David McRitchie has done most of it for you: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) OMICOE wrote: I have a spreadsheet that contains hyperlinks to Word docs and pdfs of reports. The spreadsheet contains database information that can direct my users to reports that were previously written on specific subjects. The hyperlinks were all set up to be directed to docs in a specific folder on server. Recently I've added to the additional references and hyperlinks to that spreadsheet. When I open the spreadsheet yesterday, all links had changed and now show the hyperlink address to my workstation hard drive, not the server. If I add new links today, they show the server as the location, but all previously established links now refer to my workstation hard drive and I cannot connect to the document because the hyperlink directs me to the wrong place. And I don't want to re-link those 300 documents. Can you help? -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|