Using VBA to disable macros when opening files

I am using VBA to create a loop to open up a number of XL files - however I
want disable any macros that the files may contain when opening these files

Any help gratefully received

Thanks

James

James

If this is because you are trying to get around the security warning, AFAIK
there is no way.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS


"[email protected]" <[email protected]>
wrote in message news:[email protected]...
>I am using VBA to create a loop to open up a number of XL files - however I
> want disable any macros that the files may contain when opening these
> files
>
> Any help gratefully received
>
> Thanks
>
> James

Helmekki

That doesn't seem to stop the warning. It had gone past me totally and
reading help appears to state it will work, but I'd be staggered if you can
actually switch off warnings so simply on something so crucial as macro
security or am I misunderstanding

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS


"helmekki" <[email protected]> wrote in
message news:[email protected]...
>
> Try this: it will do the job
> place this code in the main file then open the other files, so the
> codes in the other files will not be excuted.
>
> the code will open the other files and prevent excuting the codes
> inside them
>
>
> Code:
> --------------------
> Sub Security()
> Dim lngAutomation As MsoAutomationSecurity
>
> With Application
> lngAutomation = .AutomationSecurity
> .AutomationSecurity = msoAutomationSecurityForceDisable
> With .FileDialog(msoFileDialogOpen)
> .Show
> .Execute
> End With
> .AutomationSecurity = lngAutomation
> End With
>
> End Sub
>
> --------------------
>
>
> --
> helmekki
>
>
> ------------------------------------------------------------------------
> helmekki's Profile:
> http://www.excelforum.com/member.php...fo&userid=6939
> View this thread: http://www.excelforum.com/showthread...hreadid=384353
>

Nick,
After reading:
http://msdn.microsoft.com/library/de...us/vbaxl10/htm
l/xlproAutomationSecurity.asp

I am confused as well.
Does this mean I can dictate Excel2002 macro security from code,
irrespective of the user's setting ?

As I do not have XL2002 I cannot test.

NickHK


"Nick Hodge" <[email protected]> wrote in message
news:#[email protected]...
> Helmekki
>
> That doesn't seem to stop the warning. It had gone past me totally and
> reading help appears to state it will work, but I'd be staggered if you
can
> actually switch off warnings so simply on something so crucial as macro
> security or am I misunderstanding
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> [email protected]HIS
>
>
> "helmekki" <[email protected]> wrote
in
> message news:[email protected]...
> >
> > Try this: it will do the job
> > place this code in the main file then open the other files, so the
> > codes in the other files will not be excuted.
> >
> > the code will open the other files and prevent excuting the codes
> > inside them
> >
> >
> > Code:
> > --------------------
> > Sub Security()
> > Dim lngAutomation As MsoAutomationSecurity
> >
> > With Application
> > lngAutomation = .AutomationSecurity
> > .AutomationSecurity = msoAutomationSecurityForceDisable
> > With .FileDialog(msoFileDialogOpen)
> > .Show
> > .Execute
> > End With
> > .AutomationSecurity = lngAutomation
> > End With
> >
> > End Sub
> >
> > --------------------
> >
> >
> > --
> > helmekki
> >
> >
> > ------------------------------------------------------------------------
> > helmekki's Profile:
> > http://www.excelforum.com/member.php...fo&userid=6939
> > View this thread:
http://www.excelforum.com/showthread...hreadid=384353
> >
>
>

What I want to do is to audit files. However I don't want to start any
Auto_Open macros when I open a file. I have listed below a cut down version
of the macro I am using. When I open up the first file the macro just stops.
Any thoughts?


Sub AuditFiles()
Dim iFilename As Object
Dim lngAutomation As MsoAutomationSecurity

lngAutomation = Application.AutomationSecurity

For Each iFilename In Range("FilesToAudit")
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Workbooks.Open FileName:=iFilename, ReadOnly:=True, password:="",
updatelinks:=0
'test workbook

Application.AutomationSecurity = lngAutomation
Workbooks(iFilename).Close savechanges:=False
Next iFilename
End Sub

"helmekki" wrote:

>
> Try this: it will do the job
> place this code in the main file then open the other files, so the
> codes in the other files will not be excuted.
>
> the code will open the other files and prevent excuting the codes
> inside them
>
>
> Code:
> --------------------
> Sub Security()
> Dim lngAutomation As MsoAutomationSecurity
>
> With Application
> lngAutomation = .AutomationSecurity
> .AutomationSecurity = msoAutomationSecurityForceDisable
> With .FileDialog(msoFileDialogOpen)
> .Show
> .Execute
> End With
> .AutomationSecurity = lngAutomation
> End With
>
> End Sub
>
> --------------------
>
>
> --
> helmekki
>
>
> ------------------------------------------------------------------------
> helmekki's Profile: http://www.excelforum.com/member.php...fo&userid=6939
> View this thread: http://www.excelforum.com/showthread...hreadid=384353
>
>

I managed to get round the problem by creating a new instance of Excel to
open the files and disabling the macros there

Thanks for pointing me in the right direction


"[email protected]" wrote:

> What I want to do is to audit files. However I don't want to start any
> Auto_Open macros when I open a file. I have listed below a cut down version
> of the macro I am using. When I open up the first file the macro just stops.
> Any thoughts?
>
>
> Sub AuditFiles()
> Dim iFilename As Object
> Dim lngAutomation As MsoAutomationSecurity
>
> lngAutomation = Application.AutomationSecurity
>
> For Each iFilename In Range("FilesToAudit")
> Application.AutomationSecurity = msoAutomationSecurityForceDisable
> Workbooks.Open FileName:=iFilename, ReadOnly:=True, password:="",
> updatelinks:=0
> 'test workbook
>
> Application.AutomationSecurity = lngAutomation
> Workbooks(iFilename).Close savechanges:=False
> Next iFilename
> End Sub
>
> "helmekki" wrote:
>
> >
> > Try this: it will do the job
> > place this code in the main file then open the other files, so the
> > codes in the other files will not be excuted.
> >
> > the code will open the other files and prevent excuting the codes
> > inside them
> >
> >
> > Code:
> > --------------------
> > Sub Security()
> > Dim lngAutomation As MsoAutomationSecurity
> >
> > With Application
> > lngAutomation = .AutomationSecurity
> > .AutomationSecurity = msoAutomationSecurityForceDisable
> > With .FileDialog(msoFileDialogOpen)
> > .Show
> > .Execute
> > End With
> > .AutomationSecurity = lngAutomation
> > End With
> >
> > End Sub
> >
> > --------------------
> >
> >
> > --
> > helmekki
> >
> >
> > ------------------------------------------------------------------------
> > helmekki's Profile: http://www.excelforum.com/member.php...fo&userid=6939
> > View this thread: http://www.excelforum.com/showthread...hreadid=384353
> >
> >

NIckH, james.

tested in Excel 2002 SP3, Win2K SP4. Macro Security = medium.

BTW James, which excel version are u using?


With Application
.AutomationSecurity = msoAutomationSecurityForceDisable
.Workbooks.Open "D:\Personal Data\book1.xls"
End With

Does not show a security promt, while there is a code module in the
workbook.

PS the bevaior if this settings changed slightly in excel 2003
(http://www.kbalertz.com/kb_825939.aspx)

DM Unseen

Win 2K SP4, Excel 2003

However, I wanted to run this in Excel 97 (...I know...long story), but it
appears that 97 doesn't have automation security

James


"DM Unseen" wrote:

> NIckH, james.
>
> tested in Excel 2002 SP3, Win2K SP4. Macro Security = medium.
>
> BTW James, which excel version are u using?
>
>
> With Application
> .AutomationSecurity = msoAutomationSecurityForceDisable
> .Workbooks.Open "D:\Personal Data\book1.xls"
> End With
>
> Does not show a security promt, while there is a code module in the
> workbook.
>
> PS the bevaior if this settings changed slightly in excel 2003
> (http://www.kbalertz.com/kb_825939.aspx)
>
> DM Unseen
>
>

James,
I have the same problem. I want to disable macros and events (running on the file that is opened with VBA) when opening a file with VBA. Since I use Excel 2000 I cannot use AutomationSecurity property. That is why I'm curious about how You did get around this problem.

I tried to use
Application.EnableEvents = False
, but it does not work. Events runs anyway.

Please send a code example if it's possible.

Thanks

/Patrik

Hi,

Have you tried holding down the 'shift' key while opening your workbook?

I'm not sure if it works on all versions of excel, but I use it from time to
time (same thing may work for Access unless it's disabled).

Cheers,
--
Kevin Lehrbass
[email protected]
www.spreadsheetsolutions4u.com


"patrikj" wrote:

>
> James,
> I have the same problem. I want to disable macros and events (running
> on the file that is opened with VBA) when opening a file with VBA.
> Since I use Excel 2000 I cannot use AutomationSecurity property. That
> is why I'm curious about how You did get around this problem.
>
> I tried to use
> Application.EnableEvents = False
> , but it does not work. Events runs anyway.
>
> Please send a code example if it's possible.
>
> Thanks
>
> /Patrik
>
>
> --
> patrikj
> ------------------------------------------------------------------------
> patrikj's Profile: http://www.excelforum.com/member.php...o&userid=29604
> View this thread: http://www.excelforum.com/showthread...hreadid=384353
>
>