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
"Tom Ogilvy" wrote in message
...
http://support.microsoft.com/kb/211626/en-us
XL2000: EnableEvents Property Does Not Work in Automation Server
--
Regards,
Tom Ogilvy
"Peter T" <peter_t@discussions wrote in message
...
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
"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
Hi Peter,
I can't reproduce any of that I'm afraid (Word/Excel 2003). What
version
of
Excel are you running?
I didn't entirely follow what you said about what you did with
different
instances of Excel but, no matter what I couldn't get open events to
run
when they shouldn't.
--
Enjoy,
Tony
"Peter T" <peter_t@discussions wrote in message
...
I'm sure when I tested earlier the Workbook_Open() didn't fire when
opened
with automation. But you are right, I stand humbly corrected!
Not only that but disabling events does not disable the
Workbook_Open
event.
The Workbook_Open event appears to re-enable events -
'' in Word
Set oXL = CreateObject("Excel.application")
oXL.enableevents = False
Debug.Print oXL.enableevents ' False
Set oWB = oXL.Workbooks.Open(s) ' Workbook_Open() fires !
Debug.Print oXL.enableevents ' True
Something even more odd. After running the above and quitting Excel,
next
time I manually start Excel and open my WB containing open events
while
holding Shift, both open events fire - holding Shift should prevent!
Then
I
did exactly the same in a different version of Excel (not previously
started
with automation) and Shift does prevent the open events.
Regards,
Peter T
"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
In my Excel 2003, the Workbook_Open Event runs when a Workbook is
opened
via
automation - but an Auto_Open macro doesn't. Yours apparenly does
something
different so there must be some other setting of which I'm not
aware.
--
Enjoy,
Tony
"Peter T" <peter_t@discussions wrote in message
...
While not exactly the same (the applications do work
differenly),
setting
(Excel) Application.EnableEvents to False is probably what is
wanted.
In normal use disabling events would prevent the Workbook_Open()
but
would
not prevent an auto_open() macro from running.
Not sure what all the fuss is about, as I mentioned earlier with
automation
of Excel neither of the open events are called without use of
RunAutoMacros.
A case of issue - what issue!
Regards,
Peter T
"Tony Jollans" <My Forename at My Surname dot com wrote in
message
...
You're not wrong - it is version dependent; AutomationSecurity
was
introduced in XP. If you have XP or 2003, it is the same
property
in
all
apps (Word, Excel and Powerpoint anyway). If you have 2000 or
earlier,
macros are automatically trusted when documents are opened via
automation.
Looking back over the thread, it's probably the wrong answer
anyway.
The
request was for an Excel equivalent of Word's
WordBasic.DisableAutoMacros.
While not exactly the same (the applications do work
differenly),
setting
(Excel) Application.EnableEvents to False is probably what is
wanted.
--
Enjoy,
Tony
"Jonathan West" wrote in message
...
"Tony Jollans" <My Forename at My Surname dot com wrote in
message
...
The example is in the wrong application - the method works
in
both
applications so change Word to Excel (and Document to
Workbook)
and
it
should do the trick.
Depends on the version of Office being used. The
AutomationSecurity
property
certainly doesn't exist in Office 2K. It does in 2003, but
I'm
not
sure
whether it exists in Office XP.
--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup