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
|
|||
|
|||
Excel 2000 converts UNC links to relative - need to preserve UNC links!
I am using Excel 2000 (I think it happens on Excel XP too). I have
hundreds of links in a spreadsheet (to Word files, text files, and other Excel spreadsheets). When I enter my links, I always enter them in the form \\servername\sharename\directory I store this link on one fileserver, and it contains links to files on that same fileserver and a second fileserver. Excel appears to store all my links in UNC format when I first enter them. But when I save and close, then re-open the spreadsheet, all the links pointing to files on the same fileserver that the spreadsheet is on have been converted to relative links. This is a huge problem because I email this spreadsheet to others and they store the spreadsheet on their own machines. Of course, the links that were converted to relative paths don't work (but the links that remained in UNC format, the links to files on the second fileserver that I talked about above) are OK. 1) Create a spreadsheet on fileserver1, saved at \\fileserver1\dir1\excel.xls 2) Create an absolute link to a document saved at \\fileserver1\dir2\word1.doc 3) Create an absolute link to a document saved at \\fileserver2\dir3\word2.doc 4) Now check both the links just to prove that they were saved as UNC links 5) Save and exit the spreadsheet 6) Open the same spreadsheet again 7) Check the first link to the file on fileserver1. It will now be of the form "..\dir2\word1.doc" instead of "\\fileserver1\dir2\word1.doc" 8) Check the second link to the file on fileserver2. It's still fine (in UNC format) Now when these spreadsheets with these links get moved around, everything blows up. Continuing with my above example: 9) Copy this spreadsheet to another fileserver or your desktop. 10) Open the spreadsheet at the new location 11) Test the first link. It is now broken and says something like, "The address of this site is not valid. Check the address and try again". Yes, it's broken, because this link became relative! 12) Test the second link. It's still fine, since it remains as an absolute (UNC) path. I really badly need to figure out how to get Excel to honor my UNC entered links and not change them to relative. Or, I need some other advice as to how to avoid this problem. Thanks very much, Jeff P.S. When I open this same spreadsheet in Excel XP, when I drag my cursor over the link, it shows links to the files on the same fileserver in the form "file:///\\fileserver1\dir2\filename.doc". However, when I edit the Hyperlink, it shows up in the "Address" field as relative. It would seem that Excel has not forgotten my UNC path. But I've tried many different iterations of opening the Excel 2000 file in Excel XP and saving it to a 3rd fileserver (and also picking various "Save as..." types) in an effort to convince Excel to preserve my UNC links on fileserver3, but no luck. |
#2
|
|||
|
|||
Excel 2000 converts UNC links to relative - need to preserve UNC links!
|
#4
|
|||
|
|||
Excel 2000 converts UNC links to relative - need to preserve UNC links!
Jeff
I have some bad news for you. Preventing Excel from making hyperlinks relative is futile, at least in my experience. Here's a couple of points to consider, although I don't know how much help they'll be: Under File - Properties, there is a Hyperlink Base textbox. If you use this textbox, all hyperlinks will be relative to this base. Leaving this property blank causes hyperlinks to be relative to the workbook's location. Since your workbook will be in different locations for different users, this probably will not lead to a solution. I just wanted you to be aware of it in case you come up with some clever way to use it. What if you create a shortcut on Server2 that points to the root directory on Server1? I haven't tried this, but maybe you could then point all your hyperlinks to Server2 (which seems to maintain the full path). Any hyperlinks that really go to Server1 could go through this shortcut. Another way that I have solved this dilemma (although not completely to my satisfaction) is to ignore all Excel hyperlinks. I created a hidden sheet in the workbook that lists the sheet and cell address and next to it, in plain text, the hyperlink. It looks like this Sheet1!A1 ' \\server1\c\JobFolders\WIP.xls Sheet2!D3 '\\server2\EmployeeFiles\PayRates.xls Except that the second column is not actually a hyperlink because I precede it with an apostrophe. Any hyperlinks I create in the sheets point to the cell in which they reside. So in Sheet1!A1, I set up a hyperlink like this ?range("a1").Hyperlinks(1).SubAddress A1 ?range("a1").Hyperlinks(1).TextToDisplay \\server1\c\JobFolders\WIP.xls The hyperlink's TextToDisplay shows the user where to go, but the actual hyperlink points to A1 which effectively does nothing when clicked. Then I use the FollowHyperlink event to actually do the stuff. I test Target.Parent.Parent.Name & "!" & Target.Parent.Address(0,0) = cell address on my hidden sheet If it finds one, it reads the next cell over (the location of the document that I'm trying to hyperlink to) and I use Me.Parent.FollowHyperlink (FndRng.Offset(0,1).Value) where FndRng is the cell on the hidden sheet that contains the corresponding cell address. It's a lot of work to set up and editing the event sub when something changes is a b*tch, but what can you do. Hopefully something in this post will of use to you. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Jeff Adams" wrote in message om... Anybody have any hints or suggestions on this? Please? (Jeff Adams) wrote in message . com... On 9 Apr 2004 15:15:35 -0700, (Jeff Adams) wrote: Just to correct a couple of potentially confusing errors I made in my original post: I am using Excel 2000 (I think it happens on Excel XP too). I have hundreds of links in a spreadsheet (to Word files, text files, and other Excel spreadsheets). When I enter my links, I always enter them in the form (in other words, UNC format). \\servername\sharename\directory I store this link on one fileserver, and it contains links to files on ^^^^^^^^^^^^^^^^ should read, "I store this spreadsheet" not "I store this link". that same fileserver and a second fileserver. Excel appears to store all my links in UNC format when I first enter them. I have the same problem whether I type the link in by hand (i.e., just start typing "backslash backslash servername" in the cell), or whether I use the "Insert Hyperlink..." menu option. But when I save and close, then re-open the spreadsheet, all the links pointing to files on the same fileserver that the spreadsheet is on have been converted to relative links. This is a huge problem because I email this spreadsheet to others and they store the spreadsheet on their own machines. Of course, the links that were converted to relative paths don't work (but the links that remained in UNC format, the links to files on the second fileserver that I talked about above) are OK. 1) Create a spreadsheet on fileserver1, saved at \\fileserver1\dir1\excel.xls 2) Create an absolute (UNC) link to a document saved at \\fileserver1\dir2\word1.doc 3) Create an absolute (UNC) link to a document saved at \\fileserver2\dir3\word2.doc 4) Now check both the links just to prove that they were saved as UNC links 5) Save and exit the spreadsheet 6) Open the same spreadsheet again 7) Check the first link to the file on fileserver1. It will now be of the form "..\dir2\word1.doc" instead of "\\fileserver1\dir2\word1.doc" 8) Check the second link to the file on fileserver2. It's still fine (in UNC format) Now when these spreadsheets with these links get moved around, everything blows up. Continuing with my above example: 9) Copy this spreadsheet to another fileserver or your desktop. 10) Open the spreadsheet at the new location 11) Test the first link. It is now broken and says something like, "The address of this site is not valid. Check the address and try again". Yes, it's broken, because this link became relative. 12) Test the second link. It's still fine, since it remains as an absolute (UNC) path. I really badly need to figure out how to get Excel to honor my UNC entered links and not change them to relative. Or, I need some other advice as to how to avoid this problem. Thanks very much, Jeff P.S. When I open this same spreadsheet in Excel XP, when I drag my cursor over the link, it shows links to the files on the same fileserver in the form "file:///\\fileserver1\dir2\filename.doc". However, when I edit the Hyperlink, it shows up in the "Address" field as relative. It would seem that Excel has not forgotten my UNC path. But I've tried many different iterations of opening the Excel 2000 file in Excel XP and saving it to a 3rd fileserver (and also picking various "Save as..." types) in an effort to convince Excel to preserve my UNC links on fileserver3, but no luck. |
Thread Tools | |
Display Modes | |
|
|