I have created files with links to lookup tables. When I move the files and the tables to a new directory the links have to be re-established each time I open the spreadsheet. How can I get the file to "remember" where the tables have been moved. I save the file after re- estabishing the links, but still have to re-establish the links each time I open the file.
Has anyone been able to solve this frustrating problem??????????????
>-----Original Message----- >I have created files with links to lookup tables. When I >move the files and the tables to a new directory the links >have to be re-established each time I open the >spreadsheet. How can I get the file to "remember" where >the tables have been moved. I save the file after re- >estabishing the links, but still have to re-establish the >links each time I open the file.
>Has anyone been able to solve this frustrating >problem?????????????? >.
I just ried that by "save as" to another location for both open files and it does not seem to work. I still have to identify wher the linked files are located. But thanks for the try.
>-----Original Message----- >Did you do this "move" while both the child sheets and the >parent sheet where open.
>Try it with everything open and move them at the same time. >>-----Original Message----- >>I have created files with links to lookup tables. When I >>move the files and the tables to a new directory the >links >>have to be re-established each time I open the >>spreadsheet. How can I get the file to "remember" where >>the tables have been moved. I save the file after re- >>estabishing the links, but still have to re-establish the >>links each time I open the file.
>>Has anyone been able to solve this frustrating >>problem?????????????? >>.
"Stan" <swilli...@andrulis.com> wrote... >I just ried that by "save as" to another location for both >open files and it does not seem to work. I still have to >identify wher the linked files are located. But thanks >for the try.
>>-----Original Message----- >>Did you do this "move" while both the child sheets and >>the parent sheet where open.
...
Let me guess at the exact steps.
You started with, say, C:\X\Y.xls, then you moved this workbook to D:\Z\Y.xls. Then you opened C:\A\B.xls, which has links to C:\X\Y.xls, which no longer exists. Excel prompts you if you want to update the links. You click on the Yes button. Then Excel displays the File Not Found dialog since it can no longer find C:\X\Y.xls. You navigate to and select D:\Z\Y.xls and click OK. Then you open D:\Z\Y.xls, thinking that the links in C:\A\B.xls now refer to D:\Z\Y.xls. They don't! Look at the links in C:\A\B.xls - they'll still refer to C:\X\Y.xls.
You're better off NOT updating links when you open the workbook containing links. Instead, immediately issue the Edit > Links... menu command, then highlight each file in the source file list and click on the Change Source... button. Navigate to and select the workbooks containing the lists you want to link to. When done changing the source files, save the workbook.
>-----Original Message----- >"Stan" <swilli...@andrulis.com> wrote... >>I just ried that by "save as" to another location for both >>open files and it does not seem to work. I still have to >>identify wher the linked files are located. But thanks >>for the try.
>>>-----Original Message----- >>>Did you do this "move" while both the child sheets and >>>the parent sheet where open. >....
>Let me guess at the exact steps.
>You started with, say, C:\X\Y.xls, then you moved this workbook to >D:\Z\Y.xls. Then you opened C:\A\B.xls, which has links
to C:\X\Y.xls, which
>no longer exists. Excel prompts you if you want to update the links. You >click on the Yes button. Then Excel displays the File Not Found dialog since >it can no longer find C:\X\Y.xls. You navigate to and
select D:\Z\Y.xls and
>click OK. Then you open D:\Z\Y.xls, thinking that the links in C:\A\B.xls >now refer to D:\Z\Y.xls. They don't! Look at the links in C:\A\B.xls - >they'll still refer to C:\X\Y.xls.
>You're better off NOT updating links when you open the workbook containing >links. Instead, immediately issue the Edit > Links... menu command, then >highlight each file in the source file list and click on the Change >Source... button. Navigate to and select the workbooks
Along these same lines, is there any way to do a "relative" link or reference, similar to what you would do with a web page, whereby, you would basically tell Excel to "look for" the file to link to in either the same directory that the current (linked) file is in, or one or two levels up? Does this make sense? So that, using the same file names from below, you could move Y.xls and B.xls to the same directory, setup the links, then move both of the files to D:\Z\ directory and when you open B.xls, still have it refer to Y.xls, because it is in the same directory still?
> >-----Original Message----- > >"Stan" <swilli...@andrulis.com> wrote... > >>I just ried that by "save as" to another location for > both > >>open files and it does not seem to work. I still have to > >>identify wher the linked files are located. But thanks > >>for the try.
> >>>-----Original Message----- > >>>Did you do this "move" while both the child sheets and > >>>the parent sheet where open. > >....
> >Let me guess at the exact steps.
> >You started with, say, C:\X\Y.xls, then you moved this > workbook to > >D:\Z\Y.xls. Then you opened C:\A\B.xls, which has links > to C:\X\Y.xls, which > >no longer exists. Excel prompts you if you want to update > the links. You > >click on the Yes button. Then Excel displays the File Not > Found dialog since > >it can no longer find C:\X\Y.xls. You navigate to and > select D:\Z\Y.xls and > >click OK. Then you open D:\Z\Y.xls, thinking that the > links in C:\A\B.xls > >now refer to D:\Z\Y.xls. They don't! Look at the links in > C:\A\B.xls - > >they'll still refer to C:\X\Y.xls.
> >You're better off NOT updating links when you open the > workbook containing > >links. Instead, immediately issue the Edit > Links... > menu command, then > >highlight each file in the source file list and click on > the Change > >Source... button. Navigate to and select the workbooks > containing the lists > >you want to link to. When done changing the source files, > save the workbook.
"Jeff" <dadov...@obsports.com> wrote... >Along these same lines, is there any way to do a "relative" link or >reference, similar to what you would do with a web page, whereby, you would >basically tell Excel to "look for" the file to link to in either the same >directory that the current (linked) file is in, or one or two levels up? >Does this make sense? So that, using the same file names from below, you >could move Y.xls and B.xls to the same directory, setup the links, then move >both of the files to D:\Z\ directory and when you open B.xls, still have it >refer to Y.xls, because it is in the same directory still?
>Does this make sense?
...
Makes sense, is perfectly reasonable, and it just can't be done in Excel. A *LONG* time ago Microsoft wrote the original Excel version 1 for 512K Macs. Those beasts had a nonhierarchical file system and mostly only one floppy drive and no harddrive, so there could only be one file open with a given base filename. And Microsoft decreed this was Good & Sufficient, and it hasn't changed at all since. Yes, all OS's under which the most recent 3 or 4 versions of Excel runs now have hierarchical file systems spanning multiple local and networked drives, but Microsoft seems not to care to expend the resources to bring this bit of Excel functionality out of the mid 1980's.
Excel's external reference syntax/semantics preclude you from having more than one file open at a time with the same base filename. When the file is open, *only* the base filename in brackets appears in the external reference. When the file is closed, Excel prepends the drive and full directory path to the bracketted base filename, where the drive and full directory path are where that file was more recently saved or accessed, whichever is more recent. It's that rather braindead semantics that makes Excel so much more difficult to work with than 123 or Quattro Pro or StarOffice Calc or . . . These other spreadsheets let you enter relative paths like
+<<..\foo\bar.123>>A:A1..A:A1 (123)
and they will leave the path-and-filename as-is. Excel simply hasn't evolved from its original implementation in this respect. It's nice to imagine Microsoft fixing this, but it probably won't happen until well after they provide built-in support for flashing text. Microsoft seems to prefer adding eyewash to adding serious features that have been in competing spreadsheets for over a decade.
As Harlan says, unfortunately you can't just have a relative link. However, you don't have to edit the links manually. He does hit the mark, but just in case you missed it in his diatribe:
Open both books, and the link becomes relative. Now save the linked file to the new location, close it and the link in the remaining workbook is automatically updated to reflect the change, now save that book to the new location. Then delete the old files.
> Along these same lines, is there any way to do a "relative" link or > reference, similar to what you would do with a web page, whereby, you would > basically tell Excel to "look for" the file to link to in either the same > directory that the current (linked) file is in, or one or two levels up? > Does this make sense? So that, using the same file names from below, you > could move Y.xls and B.xls to the same directory, setup the links, then move > both of the files to D:\Z\ directory and when you open B.xls, still have it > refer to Y.xls, because it is in the same directory still?
> > >-----Original Message----- > > >"Stan" <swilli...@andrulis.com> wrote... > > >>I just ried that by "save as" to another location for > > both > > >>open files and it does not seem to work. I still have to > > >>identify wher the linked files are located. But thanks > > >>for the try.
> > >>>-----Original Message----- > > >>>Did you do this "move" while both the child sheets and > > >>>the parent sheet where open. > > >....
> > >Let me guess at the exact steps.
> > >You started with, say, C:\X\Y.xls, then you moved this > > workbook to > > >D:\Z\Y.xls. Then you opened C:\A\B.xls, which has links > > to C:\X\Y.xls, which > > >no longer exists. Excel prompts you if you want to update > > the links. You > > >click on the Yes button. Then Excel displays the File Not > > Found dialog since > > >it can no longer find C:\X\Y.xls. You navigate to and > > select D:\Z\Y.xls and > > >click OK. Then you open D:\Z\Y.xls, thinking that the > > links in C:\A\B.xls > > >now refer to D:\Z\Y.xls. They don't! Look at the links in > > C:\A\B.xls - > > >they'll still refer to C:\X\Y.xls.
> > >You're better off NOT updating links when you open the > > workbook containing > > >links. Instead, immediately issue the Edit > Links... > > menu command, then > > >highlight each file in the source file list and click on > > the Change > > >Source... button. Navigate to and select the workbooks > > containing the lists > > >you want to link to. When done changing the source files, > > save the workbook.
|
