How do I prevent AutoRun Macros when Programmatically Opening Workbook?

[Tony Jollans]

Hi Joseph,

If you read all the conversation that's been going on around your question
you will see that Peter has agreed elsewhere that they do run.

The mechanisms in Word and Excel are, however different - reflecting an
earlier time, perhaps, when the two applications were not as integrated as
they are now - I'm not a Microsoft apologist or confidante and that's the
best I can do

 

[Joseph Geretz]

you will see that Peter has agreed elsewhere that they do run.

So how do I stop them from running? the only suggestion I've seen on this
thread, is to tighten up macro security but won't this generate an
interactive prompt each time a document with a macro is opened? (Your
security settings prohibit macros from running, etc, etc, etc...) I'm trying
to streamline and optimize the performance of the document open, and
generating an interactive prompt is really going to be counterproductive for
me.

Thanks,

- Joseph Geretz -

 

[Tony Jollans]

By setting (Excel) Application.EnableEvents = False.

 

[Joseph Geretz]

Thanks!

By setting (Excel) Application.EnableEvents = False.

I will give this a shot.

- Joseph Geretz -

 

[Peter T]

Hi Tony,

I must admit I'm puzzled about this. My original test was with XL2000, I've
since repeated with both Xl2000 & XL2003 in different systems but with
identical Word & Excel files and get different results. Namely in XL2k the
workbook open event fires and appears to re-enable events. XP works as
expected. Full details of my test -

Book2.xls contains -

'thisWorkbook module
Private Sub Workbook_Open()
Debug.Print "Workbook_Open " & ThisWorkbook.Name
Debug.Print Application.EnableEvents
End Sub

' Normal Module
Sub auto_open()
Debug.Print "auto_open " & ThisWorkbook.Name
End Sub

In a new session of windows, with no instances of Excel, I ran the following
from Word

' Word normal module
Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\My Documents\Excel\Book2.xls"
Set oXL = CreateObject("Excel.application")

oXL.enableevents = False
Debug.Print oXL.enableevents ' xl2k - False, XP-False

Set oWB = oXL.Workbooks.Open(s)
Debug.Print oXL.enableevents ' xl2k - True, XP-False

oXL.Visible = True
'oXL.enableevents = True
'oWB.RunAutoMacros 1 'xlAutoOpen

End Sub
Sub TestClose()

'oWB.RunAutoMacros 2 'xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub


After running TestOpen I look in Excel's VBA immdeiate window and read -

xl2k -
Workbook_Open Book2.xls
True

xl-xp -
empty, ie no event fired

So despite disabling events with
oXL.enableevents = False
in xl2k the Workbook_Open has fired and reset events, indeed similar re
events is
confirmed by the two debug lines in Word, before & after opening the book.
Auto_open did not fire.

In contrast to tests before my last post, now when opening the book2.xls in
xl2k with
shift depressed disables the open events as expected.

I don't know if this difference between versions is normal or due to
something else in my xl2k system.

Regards,
Peter T

 

[Tom Ogilvy]

http://support.microsoft.com/kb/211626/en-us
XL2000: EnableEvents Property Does Not Work in Automation Server

 

[Peter T]

Sorry Joseph, my original info was incorrect re the Workbook_Open event (in
ThisWorkbook module) though correct re Sub Auto_Open() in a normal module.

Disabling events "should" disable the workbook_open event but see my reply
to Tony, this doesn't do as expected in my XL2k as it does in XP.

Regards,
Peter T

PSThat was because I mistakenly read the response from Jezebel as yours.

 

[Peter T]

Thank you Tom !!

Based on the suggested Workaround for XL2000 I did the following which
works -

Create a helper xls named Book3.xls containing

' in "Module1"
Sub SetEvents(bEnable As Boolean)
Application.EnableEvents = bEnable

End Sub

' in Word
Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook
Dim oWB2 As Object

Sub TestOpen2()
Dim s1 As String, s2 As String, sPath As String
sPath = "C:\My Documents\Excel\"
s1 = "Book2.xls" ' with open events
s2 = "Book3.xls" ' with SetEvents macro

Set oXL = CreateObject("Excel.application")

If oXL.Version > 9 Then
oXL.enableevents = False
Else
Set oWB = oXL.Workbooks.Open(sPath & s2)
oXL.Run s2 & "!module1.SetEvents", False
End If

Debug.Print oXL.enableevents ' False

Set oWB = oXL.Workbooks.Open(sPath & s1)
Debug.Print oXL.enableevents ' False

oXL.Run s2 & "!module1.SetEvents", True
Debug.Print oXL.enableevents ' True

oXL.Visible = True

End Sub

Regards,
Peter T