vba, excel - detecting with xls files have a macro in them?

Question

does anyone know a programmatic way to determine whether an excel file has a macro with it? i have hundreds of excel files and i need to know which files have macros in them?

Answer 1

You can iterate through the collection Workbook.VBProject.VBComponents

If you find anything in there, you have macros

Algorightm

Loop through all XLS workbooks
    Open Workbook
        If Workbook.VBProject.VBComponents.Count > 0 Then 
            HasCodBehind = True
            '// Do what you need to here
        End If
    Close Workbook
End Loop

Answer 2

I have tried it with 2 workbooks (1 having macros & another without the macros).

Application.AutomationSecurity = msoAutomationSecurityForceDisable

Workbooks.Open("c:\temp\myfileWithMacros.xls")
Msgbox ActiveWorkBook.HasVBProject
ActiveWorkBook.Close

Workbooks.Open("c:\temp\myfileWithoutMacros.xls")
Msgbox ActiveWorkBook.HasVBProject
ActiveWorkBook.Close

Hope that helps.