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

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


--

Regards,
Nigel




"JohnH" wrote in message
...
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

Oops sorry, my logic!

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

--

Regards,
Nigel




"Nigel" wrote in message
...
Try using
Application.DisableEvents = True
' open file
Application.DisableEvents = False


--

Regards,
Nigel




"JohnH" wrote in message
...
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

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

"Nigel" wrote in message
...
Oops sorry, my logic!

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

--

Regards,
Nigel




"Nigel" wrote in message
...
Try using
Application.DisableEvents = True
' open file
Application.DisableEvents = False


--

Regards,
Nigel




"JohnH" wrote in message
...
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