Detect if Excel file has code modules 1

[RogADog]

This relates to Excel 97.

I am wanting to create a list of which of the numerous Excel files in a drive have code modules so that I can review them.

The procedure I have written is looping happily through the directories and sub-directories, opening each xl file in turn. My method of detecting if there is a module there is:

Application.VBE.CodePanes.Count

but this only returns a count of those panes which were open at the time the file was saved - so files saved with all panes closed are not being detected.

Has anyone any ideas on this?

Rog

 

[SkipVought]

Rog,

First, set a reference to Microsoft VBA Extensibility.

Second, be sure that Tools/Macro/Security - Trusted Publishers tab has Trust access to VB Project checked.

Sub HasCode()
   n = 0
   With ThisWorkbook.VBProject
      For Each vbc In .VBComponents
         n = n + vbc.CodeModule.CountOfLines
      Next
   End With
   If n > 0 Then MsgBox ThisWorkbook.Name & " has code"
End Sub

Caveat: Can't change the trusted access in other workbooks :-(

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[RogADog]

Thanks Skip, it works a treat after some initial hiccups.

For the benefit of anyone else wanting to do this in the future, the workbook being tested needs to be in the same instance of Excel as the code thats running otherwise it crashes with an automation error on the line

For Each vbc In .VBComponents

Cheers

Rog