Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Nottingham, Nottinghamshire, England
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Automation (Access 97 sr2)

    Not sure whether this one belongs here or in the excel forum, but here goes.
    We (very) occasionally maintain access databases that use excel to produce reports. The problem is that Excel doesn't always shut down properly but remains running as a process that is only visible via task manager. If the user attempts to open an excel spreadsheet without closing the access app then all they see is the Excel titlebar, menubar and toolbars but no workbooks, they can however open a fresh copy of Excel from Start and then open the spreadsheet.

    Does anyone know the cause and solution of this problem?

    TIA

    I've included the start and end code, I can post the entire code if needed.

    Dim objExcel As Excel.Application
    Set objExcel = Excel.Application

    'Do Excel stuff

    objExcel.ActiveWindow.SelectedSheets.PrintOut Copies:=1
    objExcel.ActiveWorkbook.Close
    objExcel.ActiveWorkbook.Saved = True
    objExcel.Quit
    Set objExcel = Nothing

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Automation (Access 97 sr2)

    You have to make sure that you quit Excel if you started it, even if something goes wrong. Here is skeleton code with an error handler; if Excel is running, it uses the running instance; otherwise it starts and quits Excel.

    Sub StartExcel()
    Dim appXL as Excel.Application
    Dim blnStartXL As Boolean

    On Error Resume Next
    ' Check if Excel is already running
    Set appXL = GetObject(, "Excel.Application")
    If appXL Is Nothing Then
    blnStartXL = True
    ' We have to start Excel ourselves
    Set appXL = CreateObject("Excel.Application")
    If appXL Is Nothing Then
    MsgBox "Can't start Excel", vbExclamation
    Goto Exit_Handler
    End If
    End If

    On Error Goto Err_Handler

    With appXL
    ' code using Excel goes here
    ...
    ...
    End With

    Exit_Handler:
    On Error Resume Next
    If blnStartXL = True Then
    ' We must quit Excel
    appXL.Quit
    End If
    Set AppXL = Nothing

    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Handler
    End Sub

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Nottingham, Nottinghamshire, England
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation (Access 97 sr2)

    Thanks for your prompt reply Hans.

    I tried the getObject - CreateObject earlier before reverting to the original code.
    First time around it failed the getObject proceeded to the createObject did all of the code but left the Excel process open instead of quiting.
    Second time around it still failed the getObject, I was stepping through, and it proceeded to the createObject opening a 2nd instance of excel just as before...

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Nottingham, Nottinghamshire, England
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation (Access 97 sr2)

    I have a strong feeling that the original code was created with excels macro recorder and copied and pasted into access, it was littered with implicit excel properties. After changing all of these to be explicit the problem has minimised it self to not closing the original excel process. Still doesn't close it, but at least it's not opening them by the dozen any more:-)

Posting Permissions

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