Late Binding issue with Excel.Application object

[Mr.Frog.to.you]

Hi Everyone,

I am having trouble constructing a block of code that is intended to
open a workbook and read some sheet data the worksheets in it. The
code works fine when it is early bound, but when shifting to late
binding there is a problem with the .EnableEvents changing state from
False to True when I open the first workbook. This is a problem
because the workbooks are designed to start-up with a form, and when
the code opens the workbook the form is opened with it but behind all
other forms - it should not be there at all as I understand it.

My code is as follows (Standard Module):

Option Explicit

Const xlWorksheet As Long = -4167
Const xlChartSheet As Long = 3

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)

Dim folder As String
Dim Files As Variant
Dim Filename As String

Dim i As Long

Dim Proceed As Boolean

On Error GoTo ErrorHandler

folder = BrowseFolder
Files = GetAllFilesInDir(folder)

Set XL = CreateObject("Excel.Application")

XL.DisplayAlerts = False
XL.Application.DisplayAlerts = False
XL.Application.EnableEvents = False
XL.EnableEvents = False

For i = 0 To UBound(Files)
If Right(Files(i), 3) = "xls" Then

Filename = folder & "\" & Files(i)

Set WB = XL.Workbooks.Open(Filename)

Set ws = XL.ActiveWorkbook.Sheets

For Each ss In ws
Proceed = SheetTest(ss)
Debug.Print Filename & vbTab & vbTab & ss.Name & vbTab &
CStr(Proceed)
Next

Set ws = Nothing
WB.Close savechanges:=False
End If
Next

Set ws = Nothing
Set WB = Nothing
Set XL = Nothing

Exit Sub

ErrorHandler:
Debug.Print Error & vbTab & Err
Stop
Resume Next
End Sub

I will eventually shift this to a class module when I can get it to
work. Is there any reason why the .EnableEvents = False is not
remaining in the state it is set to for the Excel.Application object?
I need to stop these workbook events from being fired. Is there
another way? It would be great to be able to take this code and use it
in other MS Office applications and keep it version inpedant at the
same time (ie/ using late binding to avoid dependancies).

Using Excel 2000 9.0.8950 SP-3

Any help greatly appreciated. I am really stumped.

The Frog

 

[Jim Cone]

Code run from within a Userform module always executes with events enabled.
That is the way it is.
You can work around that by using module level or global variables.
--
Jim Cone
Portland, Oregon USA


<[email protected]>
wrote in message
Hi Everyone,

I am having trouble constructing a block of code that is intended to
open a workbook and read some sheet data the worksheets in it. The
code works fine when it is early bound, but when shifting to late
binding there is a problem with the .EnableEvents changing state from
False to True when I open the first workbook. This is a problem
because the workbooks are designed to start-up with a form, and when
the code opens the workbook the form is opened with it but behind all
other forms - it should not be there at all as I understand it.

My code is as follows (Standard Module):

Option Explicit

Const xlWorksheet As Long = -4167
Const xlChartSheet As Long = 3

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)

Dim folder As String
Dim Files As Variant
Dim Filename As String

Dim i As Long

Dim Proceed As Boolean

On Error GoTo ErrorHandler

folder = BrowseFolder
Files = GetAllFilesInDir(folder)

Set XL = CreateObject("Excel.Application")

XL.DisplayAlerts = False
XL.Application.DisplayAlerts = False
XL.Application.EnableEvents = False
XL.EnableEvents = False

For i = 0 To UBound(Files)
If Right(Files(i), 3) = "xls" Then

Filename = folder & "\" & Files(i)

Set WB = XL.Workbooks.Open(Filename)

Set ws = XL.ActiveWorkbook.Sheets

For Each ss In ws
Proceed = SheetTest(ss)
Debug.Print Filename & vbTab & vbTab & ss.Name & vbTab &
CStr(Proceed)
Next

Set ws = Nothing
WB.Close savechanges:=False
End If
Next

Set ws = Nothing
Set WB = Nothing
Set XL = Nothing

Exit Sub

ErrorHandler:
Debug.Print Error & vbTab & Err
Stop
Resume Next
End Sub

I will eventually shift this to a class module when I can get it to
work. Is there any reason why the .EnableEvents = False is not
remaining in the state it is set to for the Excel.Application object?
I need to stop these workbook events from being fired. Is there
another way? It would be great to be able to take this code and use it
in other MS Office applications and keep it version inpedant at the
same time (ie/ using late binding to avoid dependancies).

Using Excel 2000 9.0.8950 SP-3

Any help greatly appreciated. I am really stumped.

The Frog

 

[Mr.Frog.to.you]

Hi Jim,

Thankyou for the feedback. I am aware of the events system in Excel.
My issue is that when that system is disabled, and a new workbook is
opened, the events system state changes to enabled again. This is not
the behavior that is supposed to happen. The events system is part of
the application, not the workbook - the workbook has events, but they
only activate (or are fired) when events system is enabled. This is
not the behaviour that Excel is displaying when changing from early
binding to late binding.

In short, using early binding the code works, using late binding the
behaviour of the application object is different with
the .EventsEnabled property changing state without being told to do
so.

Has anyone got any ideas how to solve this?

The Frog