Getting source lists w/o opening target file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arifi Koseoglu - 23 May 2004 17:01 GMT

Hello everyone,

I need a method to extract the list of documents an excel document is
linked to *without* opening the file (or, if it definitely has to be
opened, whe need the non-unc named links .

In other words: Lets say I have an excel document called
"target.xls"
which has cells linked to (get data from) cells in files
"d:\folder1\f1.xls" and
"\\server1\share1\s1.xls"

I need a tool that returns me the following list when run on the file
target.xls:
d:\folder1\f1.xls
\\server1\share1\s1.xls

We have written a macro which tries to do the same thing by loading
the target.xls and then retrieving this info. If all links are in
\\server... form, this works fine. However for links in the form
"d:\folder1\f1.xls" the path returned differs depending on where
(which PC) the script is run, i.e. where the target.xls was opened.

We need the forms exactly as how the user who created them entered
into the cells, in order to deduce the exact file name by tracing back
the mappings on every users PC.

Maybe, the question at the top can be re-phrased as:
We need a method to extract the list of documents an excel document is
linked to *without* opening the file OR, if it definitely has to be
opened, whe need a method that returns the non-unc links in the form
they were originally created without being modified depending on where
the document was opened.

There are thousands of files so manual work is not possible.

Any pointers GREATLY appreciated.
Many thanks in advance,
-arifi

Reply to this Message

Tushar Mehta - 23 May 2004 17:25 GMT

> Hello everyone,
>
> I need a method to extract the list of documents an excel document is
> linked to *without* opening the file (or, if it definitely has to be
> opened, whe need the non-unc named links .

So, you want a list of the files referenced by the target.xls file.
Correct?

> We have written a macro which tries to do the same thing by loading
> the target.xls and then retrieving this info. If all links are in
> \\server... form, this works fine. However for links in the form
> "d:\folder1\f1.xls" the path returned differs depending on where
> (which PC) the script is run, i.e. where the target.xls was opened.

What *does* it return? What should it return? What is the relevant
code? How is it different on different PCs?

In any case, you might want to check Bill Manville's FindLink utility,
available from http://www.bmsltd.ie/MVP/MVPPage.asp

Signature

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

> Hello everyone,
>
[quoted text clipped - 35 lines]
> Many thanks in advance,
> -arifi

Reply to this Message

Arifi Koseoglu - 24 May 2004 11:33 GMT

> > I need a method to extract the list of documents an excel document is
> > linked to *without* opening the file (or, if it definitely has to be
> > opened, whe need the non-unc named links .
> >
> So, you want a list of the files referenced by the target.xls file.
> Correct?

Exactly. And I would prefer to get this information without having to first
load the document into Excel.
(IOW, I wish to be able to obtain this info with a command-line tool)

> > We have written a macro which tries to do the same thing by loading
> > the target.xls and then retrieving this info. If all links are in
[quoted text clipped - 4 lines]
> What *does* it return? What should it return? What is the relevant
> code?

This macro returns a the list of files that the document is linked do (it
returns
the list we get in the "Edit links" dialog)

> How is it different on different PCs?

Probably depending on how the link was created (not sure). The path
displayed
in the Edit Links dialog changes depending on the PC the document is opened.
On one PC it says sth like C:\path. and yet on the other the same links
shows D:\...
We need a script that returns the same list independent on where the
document
was opened.

> In any case, you might want to check Bill Manville's FindLink utility,
> available from http://www.bmsltd.ie/MVP/MVPPage.asp

Since this needs a workbook to be opened first, it is of not much use
for me. I also tried to look at its source to get some idea on other
possibilities -
but apparently the author does not want us to.

Thanks again,
Cheers
-arifi

> > Hello everyone,
> >
[quoted text clipped - 35 lines]
> > Many thanks in advance,
> > -arifi

Reply to this Message

Tushar Mehta - 25 May 2004 00:07 GMT

> Exactly. And I would prefer to get this information without having to first
> load the document into Excel.
> (IOW, I wish to be able to obtain this info with a command-line tool)

I don't know of any way to access an XL document (or an Office
document, for that matter) without using the program associated with
the document

MS used to publish the specs for the various Office binary files. I
don't believe it does so any more.

I know that the Java community has a suite (API-type access) that opens
Office documents without the associated program. You might want to
check into that.

Signature

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

> > > I need a method to extract the list of documents an excel document is
> > > linked to *without* opening the file (or, if it definitely has to be
[quoted text clipped - 81 lines]
> > > Many thanks in advance,
> > > -arifi

Reply to this Message

Bill Manville - 25 May 2004 00:13 GMT

> Exactly. And I would prefer to get this information without having to first
> load the document into Excel.
> (IOW, I wish to be able to obtain this info with a command-line tool)

It might be possible to write such a tool, which would have to decode the XLS
file format to find the external link records (no easy task). As far as I
know no-one has done so.

> We need a script that returns the same list independent on where the
> document was opened.

For links to files on the same drive as the file containing the link, this is
unlikely as such links are in effect held relatively. So the source of the
link depends on where the file containing the link is located.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

Arifi Koseoglu - 25 May 2004 17:55 GMT

Many thanks Tushar, many thanks Bill! I see that I probably should change
aim and probaby trace the links back to the machines they were originally
created on... .... Not happy. I will also look into the java toolkit Tushar
mentioned.
Cheers
-arifi

> > Exactly. And I would prefer to get this information without having to first
> > load the document into Excel.
[quoted text clipped - 14 lines]
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup

Reply to this Message