If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Getting source lists w/o opening target file
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 |
#3
|
|||
|
|||
Getting source lists w/o opening target file
"Tushar Mehta" wrote 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 \\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? 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 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... 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 |
#4
|
|||
|
|||
Getting source lists w/o opening target file
In article , says...
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. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... "Tushar Mehta" wrote 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 \\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? 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 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... 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 |
#5
|
|||
|
|||
Getting source lists w/o opening target file
Arifi Koseoglu wrote:
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 |
#6
|
|||
|
|||
Getting source lists w/o opening target file
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 "Bill Manville" wrote in message ... Arifi Koseoglu wrote: 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 |
Thread Tools | |
Display Modes | |
|
|