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

On Apr 28, 3:01*pm, Boss <B...@discussions.microsoft.com> wrote:
> 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.

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


--
Regards
Dave Hawley
www.ozgrid.com

"Boss" <(E-Mail Removed)> wrote in message
news89691D4-13B4-4628-8D1E-(E-Mail Removed)...
> Is it possible to detect if an excel file contains macros without opening
> it.
> please advise.. thx! - 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



"Javed" wrote:

> On Apr 28, 3:01 pm, Boss <B...@discussions.microsoft.com> wrote:
> > Is it possible to detect if an excel file contains macros without opening it.
> > 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.
> .
>

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


"Boss" wrote:

> 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
>
>
>
> "Javed" wrote:
>
> > On Apr 28, 3:01 pm, Boss <B...@discussions.microsoft.com> wrote:
> > > Is it possible to detect if an excel file contains macros without opening it.
> > > 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.
> > .
> >

this works great!!!

thanks a lot!


"JLatham" wrote:

> 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
>
>
> "Boss" wrote:
>
> > 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
> >
> >
> >
> > "Javed" wrote:
> >
> > > On Apr 28, 3:01 pm, Boss <B...@discussions.microsoft.com> wrote:
> > > > Is it possible to detect if an excel file contains macros without opening it.
> > > > 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.
> > > .
> > >

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

Thanks!


"Boss" wrote:

> this works great!!!
>
> thanks a lot!
>
>
> "JLatham" wrote:
>
> > 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
> >
> >
> > "Boss" wrote:
> >
> > > 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
> > >
> > >
> > >
> > > "Javed" wrote:
> > >
> > > > On Apr 28, 3:01 pm, Boss <B...@discussions.microsoft.com> wrote:
> > > > > Is it possible to detect if an excel file contains macros without opening it.
> > > > > 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.
> > > > .
> > > >