Hello all.
Is there a way of finding all files in a directory which contain a macro? I have script which will loop through all files in a folder but I don't know, and can't find, the appropriate syntax for referencing macros/modules.
TIA.
Stephen
Hello all.
Is there a way of finding all files in a directory which contain a macro? I have script which will loop through all files in a folder but I don't know, and can't find, the appropriate syntax for referencing macros/modules.
TIA.
Stephen
Re: Finding Files Containing Macros
hi Stephen,
have a look at the attached sample.
it opens the files in a folder one by one and lists the module types within
doesn't work if you dont have password for certain files
hope this helps
Re: Finding Files Containing Macros
Hi Stephen,
You could start with something simple like this:
Sub Test()
Dim wbkTest As Workbook
Set wbkTest = Application.Workbooks("DeleteMe")
MsgBox "The workbook has code : " & WbkHasCode(wbkTest)
End Sub
Function WbkHasCode(wbk As Workbook) As Boolean
Dim vbc As VBComponent
WbkHasCode = False
For Each vbc In wbk.VBProject.VBComponents
If vbc.Type = vbext_ct_StdModule Then
WbkHasCode = True
Exit Function
End If
Next
End Function
Display More
This only looks for standard modules. You may wish to look into counting procedure lines in the objects like ThisWorkbook too.
EDIT : Sorry X, didn't notice your post (and can't check files from work anyway!)
Re: Finding Files Containing Macros
Thank you gentlemen. I'm having problems with both so further help would be very welcome.
xlite -
Your script tells me there are no matching files when I know at least one file in the folder has a macro (without a password).
Richie -
I'm getting a compile error
QuoteUser-defined type not defined
on the line
Any thoughts?
Re: Finding Files Containing Macros
Could it be that the file in question doesn't have a code module, but does have code within a Sheet?
It looks like the provided code only searches for a module.
Re: Finding Files Containing Macros
Hi Stephen,
Forgot to add - you need to set a reference to the Extensibility library (Tools/References). Sorry
Re: Finding Files Containing Macros
Quote from StephenRxlite -
Your script tells me there are no matching files when I know at least one file in the folder has a macro (without a password).
hi Stephen,
i forgot to mention that you need to "close" the path with "\"
in sheet 2 cell A1:
C:\Documents and Settings\HP_Administrator\My Documents\My Excel\
notice the "\" after "My Excel" folder
hth
Re: Finding Files Containing Macros
Thanks again guys. You'll start to wish you'd never seen this thread but I'm still having problems with both.
xlite - it now lists every file in the folder, most of which do not contain any modules.
Richie - I'm getting a run-time error
QuoteMethod 'VBProject of Object_Workbook failed
on this line
I presume I added the correct reference - VB for Applications Extensibility 5.3?
Thanks again for your efforts so far.
EDIT - xlite, I may have misunderstood yours. Having looked again does it list every filename and then alongside any module names? If so, it's still not quite right as it doesn't do the latter.
Re: Finding Files Containing Macros
StephenR,
Please check to see if you are set up to access the VBProject area. In Excel, click on Tools, then Macro, then Security, and then the Trusted Publishers (or Sources) tab and check the "Trust access to Visual Basic Project" box. That should let you get the second list using xlite's code.
Re: Finding Files Containing Macros
txaggie - thanks very much. You've solved the problem at a stroke. Both methods now work.
Thanks all.
Re: Find/Locate Files/Workbooks Containing Macros
I used xslite's solution and it works great what would need to be changed in order to traverse directories?
Re: Find/Locate Files/Workbooks Containing Macros
Is it possible to adapt either technique to find MS Word documents with Macros?
Many Thanks
T Fenge
Don’t have an account yet? Register yourself now and be a part of our community!