CODEWITHSUNDEEP
excelvba
Alex Gordon
Alex Gordon

Reputation: 60731

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

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?

Upvotes: 2

Views: 869

Answers (2)

shahkalpesh
shahkalpesh

Reputation: 33474

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.

Upvotes: 2

Raj More
Raj More

Reputation: 48016

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

Upvotes: 2

Related Questions