Excel: Links to spreadsheet on network drive

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.

Avoid the problem instead of solving it department: Is moving sheet1 into the main workbook an option ? Much simpler to distribute.

>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

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

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

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

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)

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

Answer accepted

Sure,

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.