Capture Excel Events from other application

[George]

Who know how to capture excel event for example 'Workbook_BeforeClose'
from other application for example MSWord. Using WithEvents in MSWord
class doesn't work, so I guess it impossible to do that.

 

[Rob Bruce]

Who know how to capture excel event for example 'Workbook_BeforeClose'
from other application for example MSWord. Using WithEvents in MSWord
class doesn't work, so I guess it impossible to do that.

Setting a reference (early binding) to Excel and then declaring:

Private WithEvents m_objExcel As Excel.Application

Gives me access to:

Private Sub m_objExcel_WorkbookBeforeClose(ByVal Wb _
As Excel.Workbook, Cancel As Boolean)

End Sub

So it does work, and is possible.

HTH

 

[Jake Marx]

Hi George,

As Rob said, this is definitely possible.

Create a class module named "CXLWBSink" and add the following code to it:

Public WithEvents xlWB As Excel.Workbook

Private Sub xlWB_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Target.Cells.Interior.ColorIndex = 3
End Sub

Now, create a standard module and add the following code to it:

Dim CxlwbSink As CxlwbSink

Sub StartDemo()
Dim xlApp As Excel.Application

Set CxlwbSink = New CxlwbSink

Set xlApp = New Excel.Application

Set CxlwbSink.xlWB = xlApp.Workbooks.Open("C:\test.xls")
xlApp.UserControl = True
xlApp.Visible = True

Set xlApp = Nothing
End Sub

Sub EndDemo()
Dim xlApp As Excel.Application

On Error Resume Next
If Not CxlwbSink.xlWB Is Nothing Then
Set xlApp = CxlwbSink.xlWB.Application
CxlwbSink.xlWB.Close False
xlApp.Quit
Set xlApp = Nothing
End If
Set CxlwbSink = Nothing
End Sub


Now, if you run StartDemo, you should get a new Excel app with test.xls open
(assuming that's a valid file path). If you change any cell in that
workbook, the cell fill color should change. When you're done, run EndDemo
to kill the instance of Excel.

This is totally off the cuff, so I'm sure you'll need to add some error
handling and tighten the code up, but I just wanted to show you that it does
work.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[George Eskildsen]

Thanks a lot for your reply. It is really help. But I wonder if it work
using late binding for example Getobject(,Excel.Application).

Thanks again

George

 

[George Eskildsen]

Great example. Amazing!different application can work together in this
way

Thank you

George

 

[Rob Bruce]

Thanks a lot for your reply. It is really help. But I wonder if it
work using late binding for example Getobject(,Excel.Application).

Well, the withevents declaration doesn't work with late binding. However,
GetObject is NOT late binding. If you assign the return value of GetObject
to an early-bound variable, then withevents will work.

Rob