Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel: Links to spreadsheet on network drive

Posted on 2000-04-18
15
Medium Priority
?
500 Views
Last Modified: 2008-01-09
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
Comment
Question by:Astroman
15 Comments
 
LVL 13

Expert Comment

by:cri
ID: 2729332
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
 
LVL 13

Expert Comment

by:cri
ID: 2729334
Avoid the problem instead of solving it department: Is moving sheet1 into the main workbook an option ? Much simpler to distribute.
0
 
LVL 1

Author Comment

by:Astroman
ID: 2731092
>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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
LVL 1

Author Comment

by:Astroman
ID: 2731503
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
 
LVL 1

Author Comment

by:Astroman
ID: 2731514
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
 
LVL 1

Author Comment

by:Astroman
ID: 2731550
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
 
LVL 1

Author Comment

by:Astroman
ID: 2731598
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
 
LVL 13

Expert Comment

by:cri
ID: 2731728
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
 
LVL 1

Author Comment

by:Astroman
ID: 2731760
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
 

Expert Comment

by:LeReveur
ID: 2734618
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
 
LVL 13

Accepted Solution

by:
cri earned 222 total points
ID: 2851529
I hope you do not mind me upgrading to answer now.
0
 
LVL 1

Author Comment

by:Astroman
ID: 2852541
Answer accepted
0
 
LVL 1

Author Comment

by:Astroman
ID: 2852542
Sure,

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

Astroman
0
 
LVL 13

Expert Comment

by:cri
ID: 2852973
ThBnks...
0
 

Expert Comment

by:veernis
ID: 2862687
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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

Read this tutorial to learn how to fix repeating password error prompts when setting up Gmail IMAP with Microsoft Outlook. The entire process is described with step by step, illustrated instructions. Enjoy...
You can use the network upload option and the Office 365 Import service to bulk-import PST files to user mailboxes. Network upload means that you upload the PST files a temporary storage area in the Microsoft cloud.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question