script to scan folders for excel files with links or marcos

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?
neoptoentAsked:
Who is Participating?
 
redmondbConnect With a Mentor Commented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
I will accept anything
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
redmondbCommented:
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
 
neoptoentAuthor Commented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
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
 
neoptoentAuthor Commented:
Oh also i think we will run through excel 2010
0
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
number of urls, and possible listing of them.
0
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
What do I put in the Attrib file?
0
 
redmondbCommented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
What if we just wanted to give a number of how many links there were?
0
 
neoptoentAuthor Commented:
Brian,

Would tht work?
0
 
redmondbCommented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
Thanks

Type mismatch
 For Each lSource In ActiveWorkbook.LinkSources

Also how will it deal with VBA that is password protected?
0
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
wish i could, but the file is confidential.... but this might have a password in itm could that break it?
0
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
How about a link in a protected cell?
Also I see on elink that is like \\server1\test\"workbooks1,"bbbb")&........
0
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
I think the password protected was teh issue.
What does the "ranges" mean?
0
 
redmondbCommented:
neoptoent,

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

Regards,
Brian.
0
 
neoptoentAuthor Commented:
under the "type header" there is range name and under object level there is range
0
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
Ownership on the details page
but yes just columns a-f
0
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
I think we are at the point where it is working correctly, just want to run some more tests
0
 
neoptoentAuthor Commented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
Great,I think the results are great

Can we add those final items like Ownership and also last modified?
0
 
redmondbCommented:
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
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
doing some final testing!!! thanks
0
 
redmondbCommented:
Still here!
0
 
neoptoentAuthor Commented:
Brian,

Do you have the code for adding the owner?
The masterfile just has the results
0
 
neoptoentAuthor Commented:
Hi,

Sorry for the late response.
Keep getting error on
Set xProj = xbook.VBProject
0
 
redmondbCommented:
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
 
neoptoentAuthor Commented:
Ok,

Not getting all user perms on the file
0
 
redmondbCommented:
neoptoent,

Could you be more specific, please?

Thanks,
Brian.
0
 
neoptoentAuthor Commented:
I was looking to get all the users who have access to the file and what level of perms they have
0
 
redmondbCommented:
Thanks, neoptoent.

Do you still want me to look into the permissions?
0
All Courses

From novice to tech pro — start learning today.