Detect macro in excel file without opening it

B

Boss

Is it possible to detect if an excel file contains macros without opening it.
please advise.. thx! - Boss
 
J

Javed

Is it possible to detect if an excel file contains macros without openingit.
please advise.. thx! - Boss

In Excel 2007 if a workbook contains macro it's extension will be xlsm
In excel 2003 no such way.For avoiding macro virus you can set the
tools-Option-security-macro security- to low so that no macro is run.
 
O

ozgrid.com

Yes, if you have security set to medium Excel will give you a choice to
enable macros BEFORE the Workbook opens. Excel 2007 macro workbooks will be
called *.xlsm
 
B

Boss

Actually i need to scan my c:\ drive and get a report that how many files
contains macros and how many does not!!

I open all the files one by one using Workbooks.Open (FileItem) methd...

Then i check does it contains macros using

For Each m In ActiveWorkbook.VBProject.VBComponents
If m.Type = 1 Then ckhcode = 5
Next m

I need to do this without opening the file, because sometimes the files
contains links to other files etc etc... and the macro hangsup...

please advise..

Gaurav
 
J

JLatham

In your macro at the point you're opening the other workbook, inhibit
updating links and alerting the user for option inputs. I'd also recommend
opening it for read only, and inhibiting any macros that may try to run
anyhow. Try this and see if it helps:

Application.DisplayAlerts=False
Application.EnableEvents=False
'modify your workbook open code here
'open the workbook without updating links, and in Read Only Mode
Workbooks.Open "somefilename.xls", False, True
Application.EnableEvents=True
Application.DisplayAlerts=True
'now do your testing for VBA contents
'and end up by closing the workbook without saving changes
ActiveWorkbook.Close False
 
B

Boss

this works great!!!

thanks a lot!


JLatham said:
In your macro at the point you're opening the other workbook, inhibit
updating links and alerting the user for option inputs. I'd also recommend
opening it for read only, and inhibiting any macros that may try to run
anyhow. Try this and see if it helps:

Application.DisplayAlerts=False
Application.EnableEvents=False
'modify your workbook open code here
'open the workbook without updating links, and in Read Only Mode
Workbooks.Open "somefilename.xls", False, True
Application.EnableEvents=True
Application.DisplayAlerts=True
'now do your testing for VBA contents
'and end up by closing the workbook without saving changes
ActiveWorkbook.Close False
 
B

Boss

This works great no doubt..!!
But just to know is it possible to detect without opening the file...?

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top