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?

shahkalpesh · Accepted Answer · 2009-12-17 19:17:09Z

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.

answered Dec 17, 2009 at 19:17

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Raj More · Accepted Answer · 2009-12-17 18:49:34Z

2

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

answered Dec 17, 2009 at 18:49

Raj More

48.2k34 gold badges139 silver badges199 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related