Detect macro in excel file without opening it

  • Thread starter Thread starter Boss
  • Start date Start date
B

Boss

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

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.
 
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
 
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
 
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
 
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
Click to expand...
 
This works great no doubt..!!
But just to know is it possible to detect without opening the file...?

Thanks!
 
Back
Top