Office Automation
I have a project where I need to go through a network share and analyze the office files I find. I need build a list of all the found files including a sublist for each of their linked and/or embedded files.
I will need that list and then to adjust all the names within to meet a naming requirement and modify the office file links and names accordingly.
I will need that list and then to adjust all the names within to meet a naming requirement and modify the office file links and names accordingly.
cy_hung
What's the meaning for linked and/or embedded files ? do you means the hyperlinks in either word / excel ?
ASKER
Yes. You can add hyperlinks to files as well as use sections of spreadsheets in another file for calculations in a "maste" spreadsheet.
could we see a sample?
so you need a function/script that will
(1) search/look at a particular location/folder and all subfolders on the drive for OFFICE files (ie xls, doc, ppt, etc)
Please specify what kind of files are you looking for
(2) open the files and inspect them in regards of hyperlinks
If a hyperlink is found write its name and location to a list
correct?
(1) search/look at a particular location/folder and all subfolders on the drive for OFFICE files (ie xls, doc, ppt, etc)
Please specify what kind of files are you looking for
(2) open the files and inspect them in regards of hyperlinks
If a hyperlink is found write its name and location to a list
correct?
ASKER
Read Office Word and Excel files from share (iterate throught sub folders) and for each found file, locate hyperlinks and references to other "some file"/workbook/sheets/cell
Examples: In myfile.xls cell A1 could refer to the following (note the filename in the formula)
=SUM([Budget.xlsx]Annual!C
=SUM('C:\Reports\[Budget.x
=SUM(Budget.xlsx!Sales)
Examples: In myfile.xls cell A1 could refer to the following (note the filename in the formula)
=SUM([Budget.xlsx]Annual!C
=SUM('C:\Reports\[Budget.x
=SUM(Budget.xlsx!Sales)
What you are asking is actually quite complex - both Word and especially Excel have several different places where they store links (e.g hyperlinks, ordinary external formula references, external links to pivot table data, external data queries, OLE links, etc). There are paid-for 3rd party products that will do this for you if you have a big requirement - Link Fixer Pro is one that I believe is good, although I haven't personally used it.
I do have some code that is part of a much bigger scanning process I wrote a while ago that identifies all types of links in Word and Excel. I will try to extract out and post just the releveant chunks of code. The other think you need to consider when doing this sort of thing is that the error handling needs to be quite comprehensive when you are looking to open arbitrary numbers Excel files, which may use add-ins you don't have access to, be password protected, etc, etc.
I do have some code that is part of a much bigger scanning process I wrote a while ago that identifies all types of links in Word and Excel. I will try to extract out and post just the releveant chunks of code. The other think you need to consider when doing this sort of thing is that the error handling needs to be quite comprehensive when you are looking to open arbitrary numbers Excel files, which may use add-ins you don't have access to, be password protected, etc, etc.
ASKER
Yes, it is quite a complex issue that requires error handling to address issues such as you have mentioned. I would love to see some relevant chunks of good. I think I spend much of my time looking for snippets that either give me more insight(s) or that I can use as a basis for some essential function(s).
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
You reference a an AddLinkItems. Can you explain this object?
OK - that's not important, it's just a routine I have to generate my output report, which is xml based. The only thing you might need to take from this is the second parameter, which is the details of the link. As you can see, I also pass the file name, the type of link, and the sheet name, but this is just for reporting purposes.