Archive Data with Excel files
 

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)?

Archive Data with Excel files
 

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.
--
Gary''s Student - gsnu200732


"hmm" wrote:

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)?

Archive Data with Excel files
 

Just to add...

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

Gary''s Student wrote:

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.
--
Gary''s Student - gsnu200732

"hmm" wrote:

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)?

--

Dave Peterson

Archive Data with Excel files
 

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?

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200732


"hmm" wrote:

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)?

Archive Data with Excel files
 

... 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.