Networked Links Change With Each User

[Karl Burrows]

We have several workbooks that contain links to a main consolidated workbook
and history files (using Excel 2000 and XP). I'm not sure how the linked
workbooks got setup, but each time I connect to the workbooks from another
source, the links have to be updated. Here are a few examples:

1. If I map a drive to the server from a client and open the workbooks, the
links have to be updated to reflect the new path.

2. If I VPN into the server and map a drive to the folder that has these
files, the links have to be changed yet again.

I have created workbooks with links many times and never had an issue with
having to relink workbooks based on the connection type. I thought the
workbooks used the UNC path for links and so it didn't matter what drive
letter you were accessing the data from.

Any ideas on how to avoid having to recreate links all the time??

Thanks!!

 

[Jonathan Rynd]

1. If I map a drive to the server from a client and open the
workbooks, the links have to be updated to reflect the new path.

2. If I VPN into the server and map a drive to the folder that has
these files, the links have to be changed yet again.

You're mapping a drive, which means assigning it to a drive letter. If the
drive letter is different, the links need to be redone.

Will it work if you *don't* map the drive and instead use UNC names
exclusively?

 

[Karl Burrows]

The files and linked files are all in the same folder/subfolders, so the
links should remain the same regardless of how it is accessed. It's the way
the client computer access it (mapped drive in network, VPN, etc.)

1. If I map a drive to the server from a client and open the
workbooks, the links have to be updated to reflect the new path.

2. If I VPN into the server and map a drive to the folder that has
these files, the links have to be changed yet again.

You're mapping a drive, which means assigning it to a drive letter. If the
drive letter is different, the links need to be redone.

Will it work if you *don't* map the drive and instead use UNC names
exclusively?

 

[Jonathan Rynd]

The files and linked files are all in the same folder/subfolders, so the
links should remain the same regardless of how it is accessed.

Excel isn't quite that smart. Visual C has the capability for relative
links (links that are in the same place as the current file) but Excel
doesn't. Excel only stores absolute links. So Excel has to change the
links depending on how the file is accessed.

 

[Karl Burrows]

So even if the path links don't change for the files themselves, the way
they are accessed is going to affect the files?

The files and linked files are all in the same folder/subfolders, so the
links should remain the same regardless of how it is accessed.

Excel isn't quite that smart. Visual C has the capability for relative
links (links that are in the same place as the current file) but Excel
doesn't. Excel only stores absolute links. So Excel has to change the
links depending on how the file is accessed.

 

[Jonathan Rynd]

So even if the path links don't change for the files themselves, the way
they are accessed is going to affect the files?

I'm not quite sure I understand you properly (what do you mean by "path
links"?) but I'm pretty sure the answer is yes. If the drive mapping
changes, Excel will need to change the links.

 

[Karl Burrows]

Yea, I'm confused too!!

I have a folder with an Excel file that has links to another folder within
this main folder with some history info files. The files in the subfolder
populate the main workbook tabs so they can see their order history. I have
already created the files and established the links on the server. If I
share the folder as "test", if you browse the network and open the main
file, it asks for the location of the links. If I were to map a drive
letter to the folder, the same thing happens. VPN connections with network
favorites as a location create the same thing. If I decide to update the
links to work for the VPN connection, then the links don't refresh on the
others and vice versa.

Does that make sense? I always thought that as long as the location of
linked files doesn't move, the links work regardless of how you access the
main file. The main file knows the location, not the path in which you
access the main file. I email folders with linked files all the time and as
long as they don't move the files from within the folder, they open even on
another network. Just not sure what is going on here.

Thanks!!

So even if the path links don't change for the files themselves, the way
they are accessed is going to affect the files?

I'm not quite sure I understand you properly (what do you mean by "path
links"?) but I'm pretty sure the answer is yes. If the drive mapping
changes, Excel will need to change the links.

 

[Jonathan Rynd]

I have
already created the files and established the links on the server.

So when you established the links initially, they were of the form
"c:\mainfolder\subfolder\file.ext"

If I share the folder as "test", if you browse the network and open the
main file, it asks for the location of the links.

From a different machine, the c:\mainfolder\subfolder\file.ext is no
longer there. So it has to recreate the links. The only place it knows
about is\\server\drive\mainfolder\, so that's what the links get created
as.

If I were to map a drive letter to the folder, the same thing happens.

Again, this is a different path: J:\mainfolder\subfolder\file.ext and
Excel doesn't know about this, so it has to ask how to reattach the
links.

VPN connections with network favorites as a location create the same
thing.

I'm not familiar with network favorites, but I assume that there is yet
another syntax used, which is not the same as any of the previous
syntaxes.

If you want links to work, you have to choose ONE and only ONE method of
accessing the files, and make sure everyone who uses the file accesses it
by that method. Or you have to write a VB macro that updates the links
to the appropriate path.

I always thought that as long as the location of linked files doesn't
move,

The *physical* location of the files isn't moving, but the *logical*
location of the files (i.e. how you're getting there) is. There's no way
to store the physical location of the files -- that notion doesn't exist
(although UNC comes close). You have to store the logical location --
how you get there. And each method of accessing the files has a
different route to get there. So from Excel's point of view, the linked
files *are* moving.

The main file knows the location,

No. The main file knows how the links were accessed last time. That's
not the same as the location. It's a set of directions on how to get to
the location.

 

[Karl Burrows]

I understand now. I'll post to the group for help with a Macro to keep the
links unless you have a suggestion.

Thanks for all the help!

I have
already created the files and established the links on the server.

So when you established the links initially, they were of the form
"c:\mainfolder\subfolder\file.ext"

If I share the folder as "test", if you browse the network and open the
main file, it asks for the location of the links.

From a different machine, the c:\mainfolder\subfolder\file.ext is no
longer there. So it has to recreate the links. The only place it knows
about is\\server\drive\mainfolder\, so that's what the links get created
as.

If I were to map a drive letter to the folder, the same thing happens.

Again, this is a different path: J:\mainfolder\subfolder\file.ext and
Excel doesn't know about this, so it has to ask how to reattach the
links.

VPN connections with network favorites as a location create the same
thing.

I'm not familiar with network favorites, but I assume that there is yet
another syntax used, which is not the same as any of the previous
syntaxes.

If you want links to work, you have to choose ONE and only ONE method of
accessing the files, and make sure everyone who uses the file accesses it
by that method. Or you have to write a VB macro that updates the links
to the appropriate path.

I always thought that as long as the location of linked files doesn't
move,

The *physical* location of the files isn't moving, but the *logical*
location of the files (i.e. how you're getting there) is. There's no way
to store the physical location of the files -- that notion doesn't exist
(although UNC comes close). You have to store the logical location --
how you get there. And each method of accessing the files has a
different route to get there. So from Excel's point of view, the linked
files *are* moving.

The main file knows the location,

No. The main file knows how the links were accessed last time. That's
not the same as the location. It's a set of directions on how to get to
the location.