Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Automation (VBA )

    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...!?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel Automation (VBA )

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

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation (VBA )

    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??

  4. #4
    Star Lounger
    Join Date
    Sep 2001
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation (VBA )

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

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Automation (VBA )

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation (VBA )

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

    Thanks

  7. #7
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation (VBA )

    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.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Automation (VBA )

    Dylan,
    I found the code I was thinking of - it's as follows:
    <pre>' 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
    </pre>

    I hope it points you in the right direction.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Star Lounger
    Join Date
    Sep 2001
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation (VBA )

    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

  10. #10
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation (VBA )

    Thanks for all the help.

    Dylan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •