Astroman
asked on
Excel: Links to spreadsheet on network drive
Assume I have a spreadsheet on a network drive: //net1/mydir/sheet.xls
The drive is mapped to g:
In the same directory, I have another sheet, with links to the first file, e.g. A1='sheet.xls'!A1
I have 2 questions:
1. Why can this not be a relative link. If I copy both sheets to another location, I want it to reference the 'local' copy, not the original.
2. Even if I have 'sheet1' open and then open sheet2, it still asks me if I want to update links, and then asks me to locate the file. It appears to be thinking that g: and //net1/ are different drives. No amount of trying to force it with Edit->Links seems to solve this.
I really would prefer the relative linking idea, but any incite into Excels mechanisms for resolving links would be much appreciated
The drive is mapped to g:
In the same directory, I have another sheet, with links to the first file, e.g. A1='sheet.xls'!A1
I have 2 questions:
1. Why can this not be a relative link. If I copy both sheets to another location, I want it to reference the 'local' copy, not the original.
2. Even if I have 'sheet1' open and then open sheet2, it still asks me if I want to update links, and then asks me to locate the file. It appears to be thinking that g: and //net1/ are different drives. No amount of trying to force it with Edit->Links seems to solve this.
I really would prefer the relative linking idea, but any incite into Excels mechanisms for resolving links would be much appreciated
Avoid the problem instead of solving it department: Is moving sheet1 into the main workbook an option ? Much simpler to distribute.
ASKER
>Avoid the problem instead of solving it department: Is moving sheet1 into the main workbook an option ? Much simpler to distribute.
Not a possibility. To make a little clearer, one sheet is a "database" of customer info, the other is a mailout that is autopopulated from it.
I checked out the knowledge base topic, and although it sounds like my problem, I have excel97 *SR-2*.
I did just have a thought though, a question before (about the formaulas updating) seemed to correlate to a bug fixed in SR-1. Is it possible that my IT department could install SR-2 witohout SR-1? Does SR-2 include SR-1, or are they separate?
Thanks for your help Cri.
Leon
Not a possibility. To make a little clearer, one sheet is a "database" of customer info, the other is a mailout that is autopopulated from it.
I checked out the knowledge base topic, and although it sounds like my problem, I have excel97 *SR-2*.
I did just have a thought though, a question before (about the formaulas updating) seemed to correlate to a bug fixed in SR-1. Is it possible that my IT department could install SR-2 witohout SR-1? Does SR-2 include SR-1, or are they separate?
Thanks for your help Cri.
Leon
ASKER
Something else I've just realisxd which is probably important:
The file with the links in is a template. If I open the original template file (*.xlt), the links are fine. If I instantiate it, thats when it asks if I want to update links in the external file (even though the sheets it is linked to are already open).
The file with the links in is a template. If I open the original template file (*.xlt), the links are fine. If I instantiate it, thats when it asks if I want to update links in the external file (even though the sheets it is linked to are already open).
ASKER
Yes. The instantiated copy has references to //net1/dir/file, and not g:/dir/file.
I have since investigated SR-2, and it says SR-1 must be installed to install it. I don't know any way of checking that the fix has been applied...
I have since investigated SR-2, and it says SR-1 must be installed to install it. I don't know any way of checking that the fix has been applied...
ASKER
I have a crappy solution:
I have saved the template as an xls file, and will just use that and save it as a different name each time. A bit clunky, but it seems to work.
I still find the whole thing weird. Have I uncovered a new excel bug? If I tell anyone, will they care?
I have saved the template as an xls file, and will just use that and save it as a different name each time. A bit clunky, but it seems to work.
I still find the whole thing weird. Have I uncovered a new excel bug? If I tell anyone, will they care?
ASKER
Latest Update:
If I open file with links through file dialog, or MRU, the links are correctly pointing to mapped drive. If I write a macro which opens the file (by recording the file open dialog), the full network path is inserted and it's broken again. GRRRR.
I went in and had a look at the code, and it was opening the file using the full network path. It then applied that path to the links. Changing it to open g:/blah/blah got it working fine. I guess the template opening mechanism uses the full network path.
-- Leon
If I open file with links through file dialog, or MRU, the links are correctly pointing to mapped drive. If I write a macro which opens the file (by recording the file open dialog), the full network path is inserted and it's broken again. GRRRR.
I went in and had a look at the code, and it was opening the file using the full network path. It then applied that path to the links. Changing it to open g:/blah/blah got it working fine. I guess the template opening mechanism uses the full network path.
-- Leon
Not sure if I understood your requirements, but how about this solution:
Workbook CustInfo contains
a) Worksheet Database
b) Worksheet MailoutTemplate
VBA/Excel Macro does the following:
1) MailoutTemplate is set up with the appropriate links
2) MailoutTemplate is copied as MailoutX
3) Modifications as needed
4) Whole content of MailoutX is "PastedSpecial" as values and format 5) Send out MailoutX
If you prefer you can populate MailoutX directly by VBA i.e. w/o using links (altough you could use _relative_ hyperlinks in this case too)
Workbook CustInfo contains
a) Worksheet Database
b) Worksheet MailoutTemplate
VBA/Excel Macro does the following:
1) MailoutTemplate is set up with the appropriate links
2) MailoutTemplate is copied as MailoutX
3) Modifications as needed
4) Whole content of MailoutX is "PastedSpecial" as values and format 5) Send out MailoutX
If you prefer you can populate MailoutX directly by VBA i.e. w/o using links (altough you could use _relative_ hyperlinks in this case too)
ASKER
My requirements are pretty much met now.
I have:
* cust_db.xls
* Mailout_template.xls {formally a .xlt, but caused problems - see comment above)
The mailout template already has a button in it which re-formats and removes all the links prior to printing and sending. It ll worked fine until I moved it onto a common network drive so the person who takes over from can use it.
Thanks for your help, now I'm just hanging out to see if anyone has any more ideas as to _why_ excel has this weird behaviour. I'll give you the points for pointing me in the right direction... (but just not yet!)
I have:
* cust_db.xls
* Mailout_template.xls {formally a .xlt, but caused problems - see comment above)
The mailout template already has a button in it which re-formats and removes all the links prior to printing and sending. It ll worked fine until I moved it onto a common network drive so the person who takes over from can use it.
Thanks for your help, now I'm just hanging out to see if anyone has any more ideas as to _why_ excel has this weird behaviour. I'll give you the points for pointing me in the right direction... (but just not yet!)
I haven't tested this in Excel to see if it solves your problem. From my experience with other applications, I have often had to code relative references to external files the "." directory as a reference point. So, to reference cell A1 in "sheet1.xls" in the current directory, I would use:
'.\sheet1.xls'A1
'.\sheet1.xls'A1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Answer accepted
ASKER
Sure,
I'd forgotton about it by now. Happy points!
Astroman
I'd forgotton about it by now. Happy points!
Astroman
ThBnks...
I had the same problem. I had about 500 spreadsheets, so I could not go in to each spreadsheet to change the formula in each cells. I wrote a VBA Macro which will update all links to the current path. This macro was put in the Personal.xls. And this macro was called in the event Workbook_Open.
XL97: Link to File on Network Is Not Updated Correctly
http://support.microsoft.com/support/kb/articles/Q172/0/65.asp
Question 1: Still looking. I _think_ you can not define relative paths w/o a work around for cell links (only for hyperlinks).
What I came up for _now_ is using the formula
=INDIRECT(INFO("directory"
If you have a lot of these you might prefer to put the path once in a cell and use the INDIRECT function to make an address
If you have a few but complicated formulas (i.e. involving LOOKUP and logic) then you might fare better if you make a clean and readable VBA solution.