Solved

Excel: Links to spreadsheet on network drive

Posted on 2000-04-18
15
475 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: dandraka
There are several quick shortcuts that can make your life easier in Microsoft Programs.  These simple tips and tricks will your work more productive and you faster at completing your tasks! MS Word (1) Creating Re-usable Scraps You can create s…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now