Excel Automation (VBA )

Topic

New Reply

I encounter a problem whenever I automate Excel from any other MS Package. I am using Windows NT and Excel 97.

I create an instance of Excel by calling the following function in an attempt to determine if Excel is already open…

‘EXCELGET – Use the FINDWINDOW API to determine if Excel is already open

Public Function EXCELGet() As Excel.Application
Dim hWnd As Long

On Error GoTo EXCEL_Error

hWnd = FindWindow(“XLMAIN”, 0)
If hWnd = 0 Then
Set EXCELGet = New Excel.Application
Else
Set EXCELGet = Excel.Application
End If

End Function

When it is a new instance of Excel being created, there is no problem and as long as I keep that instance open, Excel can continue to be automated. However, if I shut the Excel application (from Excel), the instance does not seem to been taken out of process. ‘Excel.exe’ remains in the ‘Task List’ and any further attempts to automate it result in the Toolbar/menus of Excel showing but you cannot view the workbook or sheets.

If you manually terminate the Excel.exe process, further attempts to automate Excel result in error ‘The RPC Server is unavailable’. It’s only when you shut the application/database controlling Excel that the process is removed and Excel can be automated again.

Has anyone encountered this problem, as it is driving me crazy…!?

Reply | Quote

Replies

Have you tried using Set EXCELGet = Nothing, at the end of your code than instantiates the excel session.

Andrew C

Reply | Quote

Yes, I am releasing the object variable that stores the value that the function returns, but this still does not release Excel. Obviously, after you have finished automating Excel, the session of Excel would remain open for the user to work in. It’s when they close the workbook (If there’s only 1 workbook open it, closing the workbook will terminate Excel!) or the Excel application that the application actually remains in process. Only closing the Application that automated Excel seems to destroy the process??

Reply | Quote

Do you have any code running in Excel as well or do you only open/activate Excel from another MS Office 97 program?

Reply | Quote

No, code is only running in the program that is controlling Excel.

Thanks

Reply | Quote

Hi Dylan,
FWIW I tested your code in an Access2k module (using Excel2k too) and it worked fine – if I closed the Excel app from Excel there were no processes left running. Can you post the rest of the code that actually uses this function?
I vaguely recall a code sample in the MSDN library that used the SendMessage API to register Excel in the running object table. Perhaps that would help?

Reply | Quote

Rory,

This is quite a generic piece of code which I use for any programs that need to control Excel. After I have created an object variable that represents an instance of Excel (using the function you tested), regardless of subsequent code that runs the problem remains the same.

Thanks for the SendMessage suggestion, I’ll look into this.

Reply | Quote

Dylan,
I found the code I was thinking of – it’s as follows:

' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
    "FindWindowA" (ByVal lpClassName as String, _
    ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
    "SendMessageA" (ByVal hWnd as Long,ByVal wMsg as Long _
    ByVal wParam as Long _
    ByVal lParam As Long) As Long

Sub GetExcel()
    Dim MyXL As Object                    ' Variable to hold reference
    ' to Microsoft Excel.
    Dim ExcelWasNotRunning As Boolean    ' Flag for final release.

    ' Test to see if there is a copy of Microsoft Excel already running.
    On Error Resume Next                ' Defer error trapping.
    ' Getobject function called without the first argument returns a 
    ' reference to an instance of the application. If the application isn't
    ' running, an error occurs. Note the comma used as the first argument
    ' placeholder.
    Set MyXL = Getobject(, "Excel.Application")
    If Err.Number  0 Then ExcelWasNotRunning = True
    Err.Clear                                ' Clear Err object in case error occurred.

    ' Check for Excel. If Excel is running,
    ' enter it into the Running Object table.
    DetectExcel

Set the object variable to reference the file you want to see.
    Set MyXL = Getobject("c:vb4MYTEST.XLS")

    ' Show Microsoft Excel through its Application property. Then
    ' show the actual window containing the file using the Windows
    ' collection of the MyXL object reference.
    MyXL.Application.Visible = True
    MyXL.Parent.Windows(1).Visible = True
    ' Do manipulations of your
     ' file here.
    ' ...
    ' If this copy of Microsoft Excel was not already running when you
    ' started, close it using the Application property's Quit method.
    ' Note that when you try to quit Microsoft Excel, the Microsoft Excel
    ' title bar blinks and Microsoft Excel displays a message asking if you
    ' want to save any loaded files.
    If ExcelWasNotRunning = True Then 
        MyXL.Application.Quit
    End IF

    Set MyXL = Nothing                    ' Release reference to the
                                            ' application and spreadsheet.
End Sub

Sub DetectExcel()
    ' Procedure dectects a running Excel and registers it.
    Const WM_USER = 1024
    Dim hWnd As Long
    ' If Excel is running this API call returns its handle.
    hWnd = FindWindow("XLMAIN", 0)
    If hWnd = 0 Then                    ' 0 means Excel not running.
        Exit Sub
    Else                
        ' Excel is running so use the SendMessage API 
        ' function to enter it in the Running Object Table.
        SendMessage hWnd, WM_USER + 18, 0, 0
    End If
End Sub

I hope it points you in the right direction.

Reply | Quote

I tested it using Word 97 and Excel 97(both SR-2) under Win NT 4 SP6 and could reproduce a similar behaviour:

1) I create a new instance of Excel using “New Excel.Application” and set the resulting objects visibility to True.
2) I switch to Excel manually and close Excel.
3) Excel is not closed, but only hidden.
4) But as soon as I release all object variables connected to Excel (I created the Application-object and a Worksheet-object for testing purposes.) Excel is really closed and the process “excel.exe” disappears from the task manager.

Therefore I suppose, that you do still have an active reference somewhere. Perhaps you might check, if there is an unreleased object.

Hope that helps
hedgehog

Reply | Quote

Thanks for all the help.

Dylan

Reply | Quote