slimshamus


Hello all,

I need to loop through a bunch of excel workbooks, open them, and determine if there are any macros in the workbooks, and if there are, how many.

I know how to loop through the workbooks and open them. But how can I display the names of the macros that are defined in the workbook This must be possible.

Thanks!




Re: A macro to count the number of macros in a woorkbook

Shasur


Hi here is the crude code to get the procedures from a workbook

Code Snippet

Sub Count_Program()

Dim VBP As VBProject
Dim VBModule As CodeModule
Dim VBProc As VBComponent
Dim sLastProcName As String

Set VBP = ThisWorkbook.VBProject

For Each VBM In VBP.VBComponents

Set VBModule = VBM.CodeModule

i = 1
Do Until i >= VBModule.CountOfLines

procname = VBModule.ProcOfLine(i, vbext_pk_Proc)
i = i + 1
If LenB(procname) <> 0 Then
If procname <> sLastProcName Then
MsgBox procname
sLastProcName = procname
End If
End If
Loop
'MsgBox VBModule.Name & vbCrLf & VBModule.CodeModule.CountOfDeclarationLines
Next

End Sub

You need to add the Visual Basic for Extensibility reference in the References.

Please fine tune it to loop through open workbooks

Cheers

Shasur







Re: A macro to count the number of macros in a woorkbook

slimshamus

Thanks,

I tried it and I I get an error that says "Programmatic access to Visual Basic program is not trusted" in this line "Set VBP = ThisWorkbook.VBProject". Any thoughts on that






Re: A macro to count the number of macros in a woorkbook

slimshamus

Never mind, I just had to check the box in the Macro Security settings to trust access from VB programs. Thanks again



Re: A macro to count the number of macros in a woorkbook

tracycan

Can you send me the code to open the workbooks I want to open workbooks from a list embedded in the file. Can you help Is this how you did it