Hi Everyone,
First I would like to apologize for the cross posting - I am just not
sure where the right forum is for this question. I hope that it is
here!
I have found a problem that I am hoping to find a solution for. Simply
put, when I create an Excel.Application object with late binding, set
the .EnableEvents property to False and then open a workbook,
the .EnableEvents property is 'magically' set back to true. This
problem does not exist if I use early binding. Does anyone know how /
why this is happening and /or a way to get around it.
This is a problem for me because the workbooks that I am opening have
a form set to open on the WorkBook_Open event, and this form opens
behind all other visible forms and the Excel.Application object
displays nothing of course on the taskbar. My macro code stops running
while it waits for the form to be used / closed before continuing.
This is a problem that I would like to avoid. I do not understand why
changing to late binding is producing this different set of
behaviours.
To test this yourselves, just make a blank workbook, place a blank
form (doesnt need any controls for this experiment), and have it
display with the Workbook_Open event (do leave yourself a close
button!). Open the workbook with the following code (standard Module):
Sub test()
Dim XL As Object 'Excel itself
Dim WB As Object 'Workbook
Dim WS As Object 'Worksheets Collection
Dim ss As Object 'Spreadsheet (not set, just stays as object)
On Error GoTo ErrorHandler
Set XL = CreateObject("Excel.Application")
XL.DisplayAlerts = False
XL.Application.DisplayAlerts = False
XL.Application.EnableEvents = False
XL.EnableEvents = False
Set WB = XL.Workbooks.Open(<INSERT NAME.xls OF THE WORKBOOK
FILE HERE>)
Set WS = XL.ActiveWorkbook.Sheets
For Each ss In WS
Debug.Print Filename & vbTab & vbTab & ss.Name
Next
Set WS = Nothing
WB.Close savechanges:=False
Set WS = Nothing
Set WB = Nothing
Set XL = Nothing
Exit Sub
ErrorHandler:
Debug.Print Error & vbTab & Err
Stop
Resume Next
End Sub
What you should see is that the application 'stops' running after the
Set WB = XL.Workbooks.Open line of code. It is actually still running,
just waiting for the form in the XL.Application object to be closed
before continuing. As you can see from the code above the EnableEvents
is definitely set to false, and if you watch the state of this
property in the XL.Application object you will see that it actually
changes state, all by itself, when the workbook.open method is called.
This does not happen with early binding - the EnableEvents property
stays as you put it, but it does change when using late binding and I
dont know why.
Can anyone help with this?
Excel 2000 SP3 build 9.0.8950
Cheers
The Frog
|
Similar Threads
