Application.AutomationSecurity stops calling macro

[JohnH]

Excel 2003 SP3, Vista, Macro Security et to 'Medium'

I'm trying to automatically check several hundered files, however many have
VBA including Workbook_Open events that I need to surpress. However my code
simply stops ALL macro execution including the calling macro once the
workbook is opened.

In a test file, a basic Workbook_Open event is entered, and the file saved
as Test.xls, then closed

Private Sub Workbook_Open()
MsgBox "Hello World"
End Sub

In another workbook the following macro is entered ....

Sub OpenFileTest()
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Workbooks.Open "c:\Test.xls"
Application.AutomationSecurity = msoAutomationSecurityLow
MsgBox "Done"
End Sub

If the OpenFileTest sub is run WITHOUT the
'msoAutomationSecurityForceDisable' line, then as expected the new file is
opened, followed by two message boxes, "Hello World", then "Done", but if
the 'msoAutomationSecurityForceDisable' is included the Test file is opened
then the macro just stops and I am returned to design mode.

I have also tried it in Excel 2002 (exactly the same behaviour), but on a
friends Excel 2007 it works perfectly (ie only the "Done" message box is
diplayed (sorry ... no I can't just run the code on that machine!!). I'm at
my wits end ... does anyone have any experience of this or would be so kind
to replicate the problem and let me know if it works for them or I'm missing
something simple.

John

 

[Nigel]

Try using
Application.DisableEvents = True
' open file
Application.DisableEvents = False

 

[Nigel]

Oops sorry, my logic!

Application.EnableEvents = False
' open file
Application.EnableEvents = True

--

Regards,
Nigel
(e-mail address removed)

 

[JohnH]

Thanks for that Nigel ...

Yes in hindsight the whole Application.AutomaticSecurity method (disabling
all macros in the workbook) is overkill for my needs and your solution works
perfectly.

Kind regards
John