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