Archive Data with Excel files

[Guest]

I am looking ahead to the day I will have reached my network drive allowed
capacity, and forced to offload all my data to a removable disk (CD or DVD).

Trouble is, my Excel files contain external references to files (mostly text
& other Excel files) on the network drive . When I archive the Excel and
link-target files together, how will I change the links to point to the files
in their new location (i.e., the removable disk)?

 

[Guest]

Begin experimenting before that day comes.

For example, rather than using forms like:

\\serv1\subserv1\...

use a mapped drive like G: or Z:

Another approach is to localize network references. Put the folder part of
the address in a cell and use INDIRECT to get that part. In this way you
will only have to update a single cell to update all the formulas.

 

[Dave Peterson]

Just to add...

If =indirect() is used, then the Sending workbook must be opened for excel to
recalculate without an error.

 

[Guest]

Thanks, Gary's Student.

A few comments/questions:

1. I already write to a drive called G:. Will a file still link when saved
to a CD, whose drive is usually "D:"?

2. Archive folders would probably be named and organized differently than
the original network version. Therefore, if folder-path references are
interpreted literally by Excel, the links will not work.

For this reason, I experimented: workbook A contained a link to workbook B.
I saved them both in the same folder, then moved them to another folder.
When I opened A, its link correctly pointed to B in its new location.

This suggests that one can save all Excel and linked files in one folder,
then archive them to a folder on the CD. I just need a sanity check that
this is a proper course of action, since there may be scenarios not covered
by my simple test. Any ideas?

C. Using INDIRECT is not a viable option. As Dave mentioned, all the linked
files would need to be opened.

D What about a strategy for macros? How does one specify paths in macros in
a way that the macro can "find its way" when target files are archived, i.e.,
moved to a new volume, and with different folder path?

 

[MyVeryOwnSelf]

... I experimented: workbook A contained a link to

workbook B. I saved them both in the same folder, then moved them to
another folder. When I opened A, its link correctly pointed to B in
its new location.

I did a similar experiment, with a similar result.

Things seem to work best if all the files are in the same directory, and
you copy them together to a different one.

I created the links by pointing to the other file while typing in a
formula. The link started out simply:
=[otherfile.xls]Sheet1!$A$1

After the first file is re-opened, the link gets changed to indicate the
full path name:
='C:\Documents and Settings\User\Desktop\[otherfile.xls]Sheet1'!$A$1

Then I copied both files to another volume (a flash memory card). When
opening the first file from the card, the link got changed again as one
would hope:
='F:\test\[otherfile.xls]Sheet1'!$A$1

Maybe Excel keeps track of how a link originally was defined in order to be
helpful in this way.