Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Archive LIne Items in Excel Worsheet | Excel Discussion (Misc queries) | |||
I can not save archive file in excel | Excel Discussion (Misc queries) | |||
problems to open an excel archive | Excel Discussion (Misc queries) | |||
Excel properties - 'archive'? | Excel Discussion (Misc queries) | |||
Why does Excel automatically create a temporary Word archive file. | Excel Discussion (Misc queries) |