VB to Excel communication


Posted by Guru on February 27, 2001 7:03 AM

Problem:I want to know if an instance of Excel already exists on my system thru VB code or I want Excel to inform my VB program that it is being closed whenever Excel is closed. In other words, I want to avoid Excel opening anew for each report triggered by VB.

In my application for Excel reports I am using Visual Basic as front end and MS Access as backend. From VB, I am accessing the database, doing the required calculations, creating an instance of Excel object, placing the data in an Excel worksheet and finally preparing the chart in Excel. Opening of Excel, placing data in it, drawing chart is all done thru VB code. The above process is repeated for each report I select from VB. But I want to avoid creating a new instance of Excel for each report (reports are selected randomly from a list). So through VB code I want to know if an instance of Excel already exists on my system or I want Excel to inform my VB program that
it is being closed whenever Excel is closed.

Posted by Roger Redhat on March 04, 2001 12:51 PM

You could use the GetObject method to see if Excel is running like so:-

Private Sub CheckIfExcelIsRunning()
Dim xl As Object
On Error Resume Next

Set xl = GetObject(, "Excel.Application")
If xl Is Nothing Then
Set xl = CreateObject("Excel.Application")
End If

'Now work with xl object to create chart.
End Sub

There is no Quit event for the Excel application so getting Excel to let your program know that it's quitting is going to be difficult.

Regards,
Roger.



Posted by Celia on March 06, 2001 5:25 PM


Try adding a workbook to your XLStart folder and keep the workbook hidden. Put a Workbook_Close procedure in the workbook so that you can identify (and intercept) when Excel is about to be closed and do whatever you need to do.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox ("Excel is about to close")
End Sub

Celia