MS Excel: Linking spreadsheet and Absolute and Relative Paths

[Frank G]

Our staff uses several spreadsheets, many of which are linked to
SEVERAL other spreadsheets.
Of course, all of these documents are stored on the network.

The problem we are having is the absolute path that the link uses.
Some users will access these file via (e.g.) 'S:\excel documents',
while others will use 'R:\excel documents' to connect. Each time a user
connects there is a 50-50 chance they will have to update the links.
** What a hassle**

Is there any way to use a relative path to link spreadsheets?
Using a UNC path isn't possible due to security restrictions and
changing user's mapped drives will be problematic at best.

Is there a better/easier way to approach this?

Thanks!

Frank

 

[Guest]

I'm curious.....what "security restrictions" would prevent you from using the
UNC instead of the mapped drive?

***********
Regards,
Ron

XL2002, WinXP

 

[Harlan Grove]

Ron Coderre wrote...

I'm curious.....what "security restrictions" would prevent you from using the
UNC instead of the mapped drive?

....

Perhaps the OP's IT staff believes no one should know the names of the
company's servers. However, anyone can run the console command

NET USE

and see the names of the servers to which they've mapped drive letters.
In short, security restrictions as effective as requiring at least 10
mixed case alphanumeric INTERNAL passwords in Excel files - useful only
for preventing the ignorant from making innocent, unintended changes,
useless against anyone who both knows how to use the tools Microsoft
provides with EVERY Windows install and deliberately intends to uncover
such information.

 

[Harlan Grove]

Frank G wrote...
....

The problem we are having is the absolute path that the link uses.
Some users will access these file via (e.g.) 'S:\excel documents',
while others will use 'R:\excel documents' to connect. Each time a user
connects there is a 50-50 chance they will have to update the links.
** What a hassle**

Is there any way to use a relative path to link spreadsheets?
Using a UNC path isn't possible due to security restrictions and
changing user's mapped drives will be problematic at best.

Is there a better/easier way to approach this?

If you really believe you can't use UNC pathnames (this is one of their
main intended uses), then there's always consistent drive mapping
policy. It's unlikely all your users have every letter mapped to a
drive, so it's not unlikely you could standardize on a commonly unused
drive letter, maybe Q or V.

Excel provides NO relative path capabilities whatsoever. Maybe that'll
change eventually, but Excel 2007 doesn't provide such functionality,
so you're going to have to wait until at least the next major version
release, and that's unlikely to be earlier than 2009. If you want to
handle this now, your ONLY choices are UNC pathnames, rigidly
consistent drive mapping, or an autoexecuting macro that runs when the
workbooks open that changes drive mapping based on the location of a
common marker file. The last would involve the using the ChangeLink
method of the Workbook class, basically automating what your users have
to do now.

 

[Frank G]

Thanks for the advice everyone.
The 'security' reasons I was referring too are..... the files sit in a
directory only accessible by 3 users, due to confidentially reasons.

During year-end all of the linked spreadsheets (except the confidential
ones) are temporarily moved to ANOTHER location on the server so that
10+ users have access to it. Once year-end is over, the files are moved
back.

Sounds a little silly, I know.

Anyway... if anyone has other ideas, please let me know.

Thanks again!

 

[Ragdyer]

Believe me ... from experience with users and networked templates to shared
XL dB ... standardize the drive, map them to a common ID.

 

[Harlan Grove]

Frank G wrote...
....

The 'security' reasons I was referring too are..... the files sit in a
directory only accessible by 3 users, due to confidentially reasons.

Denote that directory A.

During year-end all of the linked spreadsheets (except the confidential
ones) are temporarily moved to ANOTHER location on the server so that
10+ users have access to it. Once year-end is over, the files are moved
back.

Denote that directory B.

Moved or copied? If moved, would the year-end users of the files in B
be making any changes to the files originally from A? Would the primary
users of the files in A be making any changes to the files while
they're in B? In either case pathnames would be a fairly minor concern
compared to coordinated usage.

If no users would be making changes to the files while they're in B,
then you could make COPIES of the files from A in B and furthermore
convert ALL formulas to values. Then none of these files would have any
links between them in B.

If your open to file system trickery, and if your file server is
running Windows NT4 or later, any Unix/Linux/BSD variant or Mac OS X
or later, you could use a port of the ln command (comes as part of
Unix/Linux/BSD and Mac OS X; for Windows, you could download and
install the GnuWin32 CoreUtils package, available at

http://gnuwin32.sourceforge.net/downlinks/coreutils.php

which provides a ln command for Windows). This command can create
so-called hard links in multiple directories in the same logical disk
partition to the same file in that disk partition. To explain,
directories are a special type of file stored on disk. They contain
filenames, attributes for those files and POINTERS to the location of
the files on disk. Hard links allow file entries in different
directories to point to the same location on disk.

If your year-end users only need READ-ONLY access to the files in A,
and if they have READ-ONLY or greater access to the file server volume
(aka share) that contains A, then during year-end processing you could
create hard links to these files in another directory in that volume,
and your year-end users could access the files in that alternative
directory. Further, you could grant year-end users READ-ONLY permission
to the files during year-end processing then remove all permission for
the rest of the year. You wouldn't even need to remove the hard links;
revoking all permissions for these files from the year-end-only users
would effectively make the files invisible to them (though still
possible to confirm their existence, but not able to read, modify,
rename or delete).

 

[Harlan Grove]

Harlan Grove wrote...
....

If your year-end users only need READ-ONLY access to the files in A,
and if they have READ-ONLY or greater access to the file server volume
(aka share) that contains A, then during year-end processing you could
create hard links to these files in another directory in that volume,
and your year-end users could access the files in that alternative
directory. . . .

....

Just checked. All users would need to have just READ-ONLY access to
these files during year-end processing for hard links to work. This is
due to how Excel saves workbooks, saving as a new, temporary file
first, and if successful deleting the original file (with hard links,
that only removes the entry from the directory from which the file was
opened - if there were other links to the file, those links and the
file on disk would still remain) and renaming the temporary file with
the original file's name.