Solved

script to scan folders for excel files with links or marcos

Posted on 2013-01-16
61
886 Views
Last Modified: 2013-05-08
Hi,

I have a file server that has many directorys and sub-directorys of files.
We need to find all of the spreadhsheets that have either a link embedded or have marcos/vba code inside.

When it finds them we need to populate an excel file with the file name, owner, last used, and path

Anyone have some powershell/VB/vbscript/C# code that can do this?
0
Comment
Question by:neoptoent
  • 31
  • 30
61 Comments
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Hi, neoptoent.

Will you accept a VBA solution, i.e. a macro in a spreadsheet?

If so...
- Which version of Excel do you have?
 - You mention that you've many directories (I assume that each may have one or more sub-directories, and so on, recursively) . I see a couple of options for handling them - either the user specifies a single folder or else the macro is provided with a spreadsheet listing the top-level directories to process.

Thanks,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
I will accept anything
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, neoptoent.

Which version of Excel do you have?

You mention that you've many directories (I assume that each may have one or more sub-directories, and so on, recursively) . I see a couple of options for handling them - either the user specifies a single folder or else the macro is provided with a spreadsheet listing the top-level directories to process. Which of these do you want?

Edit: What do you mean by "link embedded"? Is that any external link or specific type(s)?

Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
We have multiple versions on Excel here. I used 2007 currently, but we would be looking through all excel file types.
Not sure which way is best.  The user could choose a top directory to start from and recurse from ther?
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, neoptoent.

We have multiple versions on Excel here. I used 2007 currently, but we would be looking through all excel file types.
Yes. May I assume that the macro will run on at least Excel 2007?

The user could choose a top directory to start from and recurse from ther?
Probably easiest for all concerned - as long as the user can go high enough up the hierarchy.

What did you mean in your original post by "link embedded"? Is that any external link or specific type(s)?

Thanks,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
Hi,

Yes 2007 or greater.

Meaning we are profiling the excel sheets, so we want to know if the excel sheet has links to other locations such as another workbook, spreadsheet,website.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, neoptoent.

Meaning we are profiling the excel sheets, so we want to know if the excel sheet has links to other locations such as another workbook, spreadsheet,website.
Presumably you want more than a simple "Yes/No"?

Probably easiest for all concerned
I've had second thoughts about this. A corrupt spreadsheet could crash Excel and lose all of the work done to that point, leaving no indication of the offending file. So instead I am proposing...
(1) Before the run start, use the Attrib command to produce a list of all files to be processed.
(2) The macro will then process each file listed in the "Attrib" file.
(3) Instead of outputting the results to a spreadsheet, they'll be written to a CSV.

This approach has a number of benefits...
(A) Complete control over the file hierarchies to be listed.
(B) Standard runs can be saved so the user doesn't have to manually select folders.
(C) Specific files and/or sub-folders can be excluded.
(D) A failed or interrupted run can be easily resumed be deleting the first n entries in the "Attrib" File
(E) If a run fails then the CSV file will identify the last successful file.
(F) Check progress by monitoring the CSV file.

Is this a one-off exercise or will the report be routinely produced in the future?

Thanks,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
That is awsome.

No this will be done regularly.

Yeah we would want more than a Y/N for link
But for the identification if there is code that exists all we would need to know is that it is there.
0
 

Author Comment

by:neoptoent
Comment Utility
Oh also i think we will run through excel 2010
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

Great, thanks.

I've been proving the value of the "Attrib" approach - it's identified a 1997 xls which crashes Excel 2010.

Yeah we would want more than a Y/N for link
Yes, I thought that might be the case. But what do you want?!

Thanks,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
number of urls, and possible listing of them.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks,  neoptoent, I'll see what I can do.

If you're interested, I've attached the work in progress. It displays the number of lines of code and the numbers of types of links. It doesn't yet do date last accessed. If you want to try it out, create your Attrib file (with an extension of .txt), click on the blue button, then browse to and select your Attrib file when prompted. (You'll probably want to change file locations from D:\.)

It's getting late here, so I probably won't be in touch until tomorrow.

Regards,
Brian.List-Excel---Copy.xlsm
0
 

Author Comment

by:neoptoent
Comment Utility
What do I put in the Attrib file?
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

Suppose you want to do the folder F:\Me\Files\Excel and all it;s subdirectories. You need to enter a DOS command (or create and run a  batch file) as follows...
attrib "F:\Me\Files\Excel\*.xls*" /s > "D:\Attrib.txt"

The click on the blue button and browse to, and select, "D:\Attrib.txt".

I've attached a batch file to do this, however Experts-Exchange doesn't allow batch files, so you'll need to rename the file to My-Attrib.bat (and change the directories!).

Regards,
Brian.My-Attrib.txt
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

Please see my previous post.

Just to manage expectations, I doubt that we're going to come up with a definitive solution as there are a number of significant issues. As a starter...
(1) Hyperlinks can be split in to two types - those using the Hyperlink function and "object" hyperlinks.
(2) "Object" Hyperlinks can be found easily, but the Hyperlink function can only be found by search all the file's formulas.
(3) Neither type of Hyperlink provides a definitive way to identify web address, email addresses and linked files.

Do you want to provide search criteria?!

Regards,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
What if we just wanted to give a number of how many links there were?
0
 

Author Comment

by:neoptoent
Comment Utility
Brian,

Would tht work?
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

I'm afraid not, the difficulty is that we'd have to find them to count them.

Did you get a chance to give the prototype a go?

(Sorry for the delay in getting back to you - a mixture of Real Life and trying to make the macro less vulnerable to corrupt files (of which I appear to have an inordinate amount - 30 of the 1500 I tested!) by opening the files in a second instance of Excel.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

Please see my my previous post.

OK, big advances. I found a macro that lists some of the data you need. I've changed it to include more data, to handle multiple files and to merger it into my earlier code. A few points...
(1) Besides the file (List_Excel_V2.xlsm) I have included two others...
     List_Excel_Test.xlsm        - This contains a number of types of links, and Hyperlinks.
     List_Excel_Test_Attrib.txt - The Attrib file for the above - assuming its in D:\.
(2) There are two outputs. The first is the summary log file you saw before.  The second is a detailed listing of ".xls" links, formulas using the Hyperlink function and "object" Hyperlinks.
The files are a total dog's dinner at the moment...
    (A) The Summary Log file information is almost all in the Report and so it's redundant.
    (B) The Link and Hyperlink totals in the report file are not as accurate as the detail entries.
    (C) The Report file should be a tab-based text file (as the summary is).
    (D) The Report file should create a unique file for each run. Instead it currently just appends if the file already exists.
    (E) The Report file's layout is rubbish, but I thought it safer to give you too much rather than too little at this stage.
(3) For some reason, Excel's Recent File List is sometimes turned off - for the moment, just go into Options and reset.
(4) The macro resets the "Text to Columns" options, so it's probably a good idea to recycle Excel after a run is finished. (Which is probably a good idea, anyway.)
(5) I need to check what happens if an external link is built without explicitly including the ".xls" string in the formula.
(6) I'd close the VBE screen while the macro's running.
(7) Don't use the clipboard in the first minute of the run. I think it's OK after that.

Ideally, you have a handful of files which cover most of the conditions you want covered and which you know well. Copy them to a test folder. Create an "Attrib" file by running the Attrib command against that folder. Then, open V2, click on the blue button and select your "Attrib file" when prompted.

Failing that, you've always got my test file.

Regards,
Brian.List-Excel-V2.xlsmList-Excel-Test.xlsmList-Excel-Test-Attrib.txt
0
 

Author Comment

by:neoptoent
Comment Utility
Hi,

Thanks so much for the response.

When I try to run i get an error " disk not ready" and the debug points to this line in the code:
Open xLOG For Append As xFileNumber
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

Please check the following lines and change as appropriate...
'location of output files
Public Const xREPORTFILE = "d:\List_Excel_Detail.csv"
Public Const xLOGBASE = "d:\List_Excel_Log_" 'Date & Time will be appended to this.


Regards,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
Ok,

Got past that error
Now stuck here:

run time error 1004
Application-defined or object defined error

  objFSOfile.writeline ",,,,,,Hyperlink," & xHyper_Type & "," & sh.Name & "!" & .Range.Address & ",,,,," & .Address & "," _
                        & .SubAddress & "," & xSubject & "," & .Name & "," & .TextToDisplay & "," & .ScreenTip & "," & xShape_Name
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

Sorry about that. Please replace the offending line by the following. This won't correct the underlying problem but it'll remove the file and the output file will help us identify the bad field.
        On Error Resume Next
            Dim xTemp As String
            xTemp = ",,,,,,Hyperlink,"
            xTemp = xTemp & xHyper_Type
            xTemp = xTemp & ","
            xTemp = xTemp & sh.Name & "!"
            xTemp = xTemp & .Range.Address
            xTemp = xTemp & ",,,,,"
            xTemp = xTemp & .Address
            xTemp = xTemp & ","
            xTemp = xTemp & .SubAddress
            xTemp = xTemp & ","
            xTemp = xTemp & xSubject
            xTemp = xTemp & ","
            xTemp = xTemp & .Name
            xTemp = xTemp & ","
            xTemp = xTemp & .TextToDisplay
            xTemp = xTemp & ","
            xTemp = xTemp & .ScreenTip
            xTemp = xTemp & ","
            xTemp = xTemp & xShape_Name
        On Error GoTo 0
        objFSOfile.writeline xTemp
    
        'objFSOfile.writeline ",,,,,,Hyperlink," & xHyper_Type & "," & sh.Name & "!" & .Range.Address & ",,,,," & .Address & "," _
                        & .SubAddress & "," & xSubject & "," & .Name & "," & .TextToDisplay & "," & .ScreenTip & "," & xShape_Name

Open in new window

Thakns,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
Thanks

Type mismatch
 For Each lSource In ActiveWorkbook.LinkSources

Also how will it deal with VBA that is password protected?
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

(1) Without deleting the links, please post a redacted copy of the file which caused the latest error.
(2) While we're at it, please post a copy of the "Hyperlink" file, also.
(3) As the comment at the start of the main macro says, it doesn't handle password-protected or hidden files. We'll revisit that when it's successfully processing ordinary files!

Thanks,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
wish i could, but the file is confidential.... but this might have a password in itm could that break it?
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

but this might have a password in itm could that break it?
Good thinking, but I'm afraid not! I tried a password-protected very hidden sheet and there's no problem listing its Hyperlinks and ordinary links.

wish i could, but the file is confidential
Well, if you can't redact them by deleting the data, what about duplicating the link (and hyperlink) entries on a new file and, assuming it gives the same error, post that?

Thanks,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
How about a link in a protected cell?
Also I see on elink that is like \\server1\test\"workbooks1,"bbbb")&........
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

I put a share address in a locked cell in a password-protected, very hidden sheet - the macro listed it.

I need to see an example of this - please either delete everything in the file except the link, or recreate that link in a new file. Please make sure that any file you post here has the error.

In the meantime, the attached surrounds the 8 (!) "For Each lSource In ActiveWorkbook.LinkSources" with a "Resume Next". (It also includes the earlier xTemp workaround.)

Regards,
Brian.List-Excel-V4.xlsm
0
 

Author Comment

by:neoptoent
Comment Utility
so when it skipped that link it works to do the rest of the file...
what i does -99999 mean when it comes to formula lines?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

... that there was an error getting the count of the lines of code. If the code is password protected then I'd expect this. Otherwise, please delete all the files data and post it here.

Thanks,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
I think the password protected was teh issue.
What does the "ranges" mean?
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

What does the "ranges" mean?
Sorry, I don't know where you're getting this from.

Regards,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
under the "type header" there is range name and under object level there is range
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

"Range Name" means that it's a defined Name, the Object Level column tells you whether it's a Worksheet or a Workbook Name, the Location column is the name of the Name and the Reference column is the Name's Refers To.

The two columns for Names that are probably critical for you are "Linked Workbook" and Full Linked File Path". (I suspect that the former is enough - unless the formula has links to two separate files.)

It's probably a good idea for you to build your own version of my List_Excel_Test.xlsm and include any links or other entries that you're interested in. Then create an "Attrib" file for just that. Knowing the data, it should be clear how it's represented (or missing) in the output file.

Regards,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
Can you assist me with buildin gthe test data

I see the description, base and variable 1.

Can you help me understand what I need to put in these fields

Thanks
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

Sorry, I didn't mean you to take me that literally! All I meant was that you would find it useful to have a file that include examples of the kinds of links etc. that your files have so that the output would be directly related to your data and so easier to understand.

An alternative would be for you to find, say, a half-dozen live files which covered the various links, etc., create an "Attrib" file for just those, run the macro against that and use the results for your testing.

Regards,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
Brian,

Do you think it is possible to have a sheet that shows just the file, path and totals?
Kinda like a an summary page
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

As a starter, please give me the results you want to see for the test file I gave you. (Better would be your own test files and their expected results.)

Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
Does info like this help?

='\\serverb\lumber\DAILY\[wood.xls]Summary'!$B$5:$BI$17
='C:\users\saws\[nostro.xls]Summary'!$B$5:$BI$17
='C:\Documents and Settings\usera\usera\Local Settings\Temporary Internet Files\OLKfdB4\[hardware.xls]testvatives'!$B$9:$K$82
='\\servera\data\bob\ProdCont\STP\Rproject\[lumber.xls]Setup'!$C$15
='\\servera\data\bob\ProdCont\STP\Rproject\[lumber.xls]Setup'!$C$15
='V:\james\userst\nails\ Report.xls]Scompletep'!$C$15
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

Sorry, I don't understand. You said you just wanted file, path and totals. The file and path are obvious, the totals considerably less so! So, I need examples of the totals - either using my test file, or, preferably, your own test files (which would have to be posted here).

Thanks,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
Looking for something similar to the log files to be created on the a first page.


Also would it be possible to list who has permissions to the files in the results?
Book4.xls
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

Thanks, but I need the files as well!

Unless you're saying that you just want V4 with just columns A to F (plus the ownership)?

Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
Ownership on the details page
but yes just columns a-f
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

Oh, I thought you wanted to replace the existing sheet. I'm not even thinking about that until the "detail" sheet has been signed off. (If you really want to play with a "summary" sheet  now, just delete columns after 4 and sort it.)

Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
I think we are at the point where it is working correctly, just want to run some more tests
0
 

Author Comment

by:neoptoent
Comment Utility
so I came across some strange results in the details:

ßïë¬td*+g¬cbHa`KK0n gäê ßU+-¦ÆAE\+UT            
Ç-o-n!=ºR a+=d'=Sv+ï--èÖ¼8~}      


Also does this find xlsxm and process them?
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

so I came across some strange results in the details:
Wow! Which column?

Also does this find xlsxm and process them?
Entirely your choice. If the Attrib command was, say, "*.xlsm", then "Yes", if ".xlsx" then "No".

Thanks,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
Great,I think the results are great

Can we add those final items like Ownership and also last modified?
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Apologies for the delay, neoptoent, I'm on this now.

A couple of points...
(1) Which column were the "strange results" being output to?
(2) Last modified is already being output.

Regards,
Brian/
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

Please see the attached. The only change is that it includes the Owner. (Please be careful that I correctly moved the remaining columns to the right!)

Regards,
Brian.Master-File-V5.xls
0
 

Author Comment

by:neoptoent
Comment Utility
doing some final testing!!! thanks
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Still here!
0
 

Author Comment

by:neoptoent
Comment Utility
Brian,

Do you have the code for adding the owner?
The masterfile just has the results
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
Comment Utility
neoptoent.

My apologies - I had posted the wrong file (it's actually from a different EE Question).

Please see attached.

Regards,
Brian.List-Excel-V5.xlsm
0
 

Author Comment

by:neoptoent
Comment Utility
Hi,

Sorry for the late response.
Keep getting error on
Set xProj = xbook.VBProject
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

The error message would help!

(If it's 1004 ("Programmatic access to Visual Basic Project is not trusted"), then go into the Excel's Options, click on the "Trust Center" tab, click on the "Trust Center Settins..." button, click on the "Macro Settings tab and make sure that the "Trust access to the VBA project object model" checkbox is ticked. "OK" your way out.)

Thanks,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
Ok,

Not getting all user perms on the file
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
neoptoent,

Could you be more specific, please?

Thanks,
Brian.
0
 

Author Comment

by:neoptoent
Comment Utility
I was looking to get all the users who have access to the file and what level of perms they have
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, neoptoent.

Do you still want me to look into the permissions?
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

This article will help you understand what HashTables are and how to use them in PowerShell.
A procedure for exporting installed hotfix details of remote computers using powershell
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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

10 Experts available now in Live!

Get 1:1 Help Now