How to automatically make excel detect directory path for linked f

[rushdhih]

We have Reporting Tool that is in Excel with a lot of Linked Data files, the
problem is when we copy these files into another persons PC, the links refer
to the previous machine and all reference to it needs to be changed. This is
very time consuming considering the number of linked cells and files that
need to be changed.

Will appreciate, if any can give me a way to overcome this problem.

Thank you
Rushdhi

 

[Mike H]

Hi,

I'm not sure I understand the problem. If you copy them to the same folder
on each machine then the links should work but if you copy the files randomly
then they won't.

You could copy the files to the My Documents directory and use
'Specialfolders' to get the path. This will get that path on each machime

MyPath = CreateObject("WScript.Shell").Specialfolders("MyDocuments")

Mike

 

[rushdhih]

I have say 3 Excel Files linked as under:
1 Reporting File (Main File) and 2 Data Files (eg Jan and Feb 09). The
Cells in Data Files are linked to Cells in the main file.

The problem is that when I copy these files into say a USB drive and give it
to a colleague to work then the file links refer to the original location of
the file and does not get updated with the new location (USB Drive) which
could be D,EF,G or what ever the drive letter that the system identifies.

My question, is there a way to get out of this drive (path problem) so that
regardless of the machine that you are working from, the links are intact

Thank you
Rushdhi