A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Excel 2000 converts UNC links to relative - need to preserve UNC links!



 
 
Thread Tools Display Modes
  #1  
Old April 9th, 2004, 11:15 PM
Jeff Adams
external usenet poster
 
Posts: n/a
Default 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  
Old April 12th, 2004, 07:05 PM
Jeff Adams
external usenet poster
 
Posts: n/a
Default Excel 2000 converts UNC links to relative - need to preserve UNC links!

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.

  #3  
Old April 14th, 2004, 08:01 PM
Jeff Adams
external usenet poster
 
Posts: n/a
Default Excel 2000 converts UNC links to relative - need to preserve UNC links!

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.

  #4  
Old April 15th, 2004, 05:46 PM
Dick Kusleika
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:58 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.