Excel Automation from Access problem

[TheFrog]

Hi Everyone,

I am having an issue with Excel automation that doesnt seem to make any sense. There appears to be a difference between early binding the code and late binding the code - and the late binding is behaving strangely.

In short I am using automation to open a workbook, check all the sheets in it for a specific data structure, and if it exists then return the data. Pretty simple it would seem. The complication is that the workbooks have a form on them that launches on the Workbook_Open event - so to get around this I set the Excel.Application objects .EnableEvents property to false. This works perfectly with early binding, but not with late binding, and I dont know why.

Can anyone help with this one?
Excel 2000 SP3 build: 9.0.8950

Cheers,

The Frog


Code is as follows:


Sub EarlyBinding()
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set xl = New Excel.Application
xl.EnableEvents = False

Set wb = xl.Workbooks.Open("Filename<INSERT HERE FILENAME>")

For Each ws In wb.Worksheets
Debug.Print ws.Name
Next

wb.Close savechanges:=False

Set wb = Nothing
Set xl = Nothing

End Sub
__________________________________________________________
****************************************************
Late Binding (form still opens):

Sub LateBinding()
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)

Set xl = CreateObject("Excel.Application")

xl.DisplayAlerts = False
xl.Application.DisplayAlerts = False
xl.Application.EnableEvents = False
xl.EnableEvents = False

'The state of the .EnableEvents property actually changes
'When the next line of code executes (to .EnableEvents = True)
Set wb = xl.Workbooks.Open("Filename<INSERT Here Filename>")

Set ws = xl.ActiveWorkbook.Sheets

For Each ss In ws
Debug.Print ss.Name
Next

Set ws = Nothing
wb.Close savechanges:=False

Set ws = Nothing
Set wb = Nothing
Set xl = Nothing

End Sub

 

[RoryA]

It's a bug - see this MSKB article.

 

[TheFrog]

Thankyou for the information. What a bugger of a thing. I will control the scenario with reference setting via a class module instead.

Much appreciated

The Frog