• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

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
0
Astroman
Asked:
Astroman
1 Solution
 
criCommented:
Question 2: See whether this applies in your case:
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") &"'sheet1.xls'!A1")

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.

0
 
criCommented:
Avoid the problem instead of solving it department: Is moving sheet1 into the main workbook an option ? Much simpler to distribute.
0
 
AstromanAuthor Commented:
>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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
AstromanAuthor Commented:
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).
0
 
AstromanAuthor Commented:
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...
0
 
AstromanAuthor Commented:
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?
0
 
AstromanAuthor Commented:
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
0
 
criCommented:
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)
0
 
AstromanAuthor Commented:
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!)
0
 
LeReveurCommented:
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
0
 
criCommented:
I hope you do not mind me upgrading to answer now.
0
 
AstromanAuthor Commented:
Answer accepted
0
 
AstromanAuthor Commented:
Sure,

I'd forgotton about it by now. Happy points!

Astroman
0
 
criCommented:
ThBnks...
0
 
veernisCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now