WithEvents between Access and Excel

[Karen Hagerman]

I posted earlier and it hasn't shown up yet, so sorry if this ends up being a repost.

I am trying to use WithEvents to enable the following actions.

From an Access form, Excel is opened to a pre-formatted Excel file.
The User then types in some values in the Excel sheet.
Once the Excel sheet/app is closed and saved, I want Access to completely close the Excel instance.

I have tried to understand and implement the WithEvents code described in a few places in MSDN. To see if I was successful, I have included a msgbox in the appropriate code but I never see the message box so I do not believe I have the code correct. My code is:

In a class labeled XLEvents

Option Compare Database

Private WithEvents xlApp As Excel.Application

Private Sub Class_Initialize()
Set xlApp = Excel.Application
End Sub

Private Sub xlApp_WindowResize(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)
MsgBox ("in the class")
End Sub

In a module labeled basXLEvents

Option Compare Database

Global gXLEvents As clsXlEvents

Sub InitXLEvents()
Set gXLEvents = New clsXlEvents
End Sub

In my Access form module I have

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim xlApp As Excel.Application 'Variables for Excel Object
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Const xlFile = "F:\Documents and Settings\Karen Hagerman\......"
Const xlSheetName = "Sheet1"

(OTHER CODE WHICH SETS UP VARIABLES TO PUT IN THE EXCEL SHEET)

'Create the Excel Object
Set xlApp = CreateObject("Excel.Application")

With xlApp
.Visible = True
Set xlBook = .Workbooks.Open(xlFile)
On Error Resume Next
Set xlSheet = xlBook.Worksheets(xlSheetName)
(MORE CODE THAT MANIPULATES THE EXCEL SHEET)

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

Private Sub xlApp_WindowDeactivate()
MsgBox ("in the form")
End Sub

Private Sub xlApp_WindowResize()
MsgBox ("intheform")
End Sub

If my code was working, I'd expect to see the message boxes when I resize or close the Excel window, neither happens. Any suggestions on what I'm doing wrong would certainly help. There must be a few real experts with WithEvents in this group.

Karen

 

[Jan Karel Pieterse]

Hi,

The code in the class module is not run because you
haven't initialised the class.

Read more about these things in:

http://www.cpearson.com/excel/AppEvent.htm

But there is no need for this class module to close excel
once your code is done.

Simply add

xlApp.Quit
Set xlApp=Nothing

when you want excel to quit.

Regards,

Jan Karel Pieterse
Excel TA/MVP

-----Original Message-----
I posted earlier and it hasn't shown up yet, so sorry if this ends up being a repost.

I am trying to use WithEvents to enable the following actions.

From an Access form, Excel is opened to a pre-formatted Excel file.
The User then types in some values in the Excel sheet.
Once the Excel sheet/app is closed and saved, I want

Access to completely close the Excel instance.

I have tried to understand and implement the WithEvents

code described in a few places in MSDN. To see if I was
successful, I have included a msgbox in the appropriate
code but I never see the message box so I do not believe I
have the code correct. My code is:

In a class labeled XLEvents

Option Compare Database

Private WithEvents xlApp As Excel.Application

Private Sub Class_Initialize()
Set xlApp = Excel.Application
End Sub

Private Sub xlApp_WindowResize(ByVal Wb As

Excel.Workbook, ByVal Wn As Excel.Window)

MsgBox ("in the class")
End Sub

In a module labeled basXLEvents

Option Compare Database

Global gXLEvents As clsXlEvents

Sub InitXLEvents()
Set gXLEvents = New clsXlEvents
End Sub

In my Access form module I have

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim xlApp As Excel.Application 'Variables for Excel Object
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Const xlFile = "F:\Documents and Settings\Karen Hagerman\......"
Const xlSheetName = "Sheet1"

(OTHER CODE WHICH SETS UP VARIABLES TO PUT IN THE EXCEL SHEET)

'Create the Excel Object
Set xlApp = CreateObject("Excel.Application")

With xlApp
.Visible = True
Set xlBook = .Workbooks.Open(xlFile)
On Error Resume Next
Set xlSheet = xlBook.Worksheets(xlSheetName)
(MORE CODE THAT MANIPULATES THE EXCEL SHEET)

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

Private Sub xlApp_WindowDeactivate()
MsgBox ("in the form")
End Sub

Private Sub xlApp_WindowResize()
MsgBox ("intheform")
End Sub

If my code was working, I'd expect to see the message

boxes when I resize or close the Excel window, neither
happens. Any suggestions on what I'm doing wrong would
certainly help. There must be a few real experts with
WithEvents in this group.

 

[JeffK]

Karen,

The only variable you declared Withevents is the one in
the standalone class module. This variable is never
initialized in your form. Instead you initialize a
separate xlApp variable, which is not declared Withevents
so it will never respond to any.

You don't need the class module or the code in the
standard module. I suggest moving the xlApp declaration
in your form to module level and including Withevents
there. The rest of your code in the form should then be
fine:

Option Compare Database
Option Explicit '---> OMG - ADD THIS!!!!!!!!!!!!!!!!!!!!!!

Private Withevents xlApp As Excel.Application

Private Sub Command5_Click()

On Error GoTo Err_Command5_Click

Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Const xlFile = "F:\Documents and Settings\Karen
Hagerman\......"
Const xlSheetName = "Sheet1"

Set xlApp = CreateObject("Excel.Application")

With xlApp
.Visible = True
Set xlBook = .Workbooks.Open(xlFile)
On Error Resume Next
Set xlSheet = xlBook.Worksheets(xlSheetName)
End With

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

Private Sub xlApp_WindowDeactivate()
MsgBox ("in the form")
End Sub

Private Sub xlApp_WindowResize()
MsgBox ("intheform")
End Sub