Automation issue? (closing Excel from Access) 1

[formerTexan]

I cannot properly close an instance of Excel that has been opened in Access.

I create an Excel app in Access and export the results of a recordset to Excel

 Dim appExcel As Excel.Application
 Dim wkbExcel As Excel.Workbook
 Dim wksExcel As Excel.Worksheet
'   Create the Excel Application and Workbook
    Set appExcel = New Excel.Application
    Set wkbExcel = appExcel.Workbooks.Add
    Set wksExcel = wkbExcel.Worksheets(1)

   'fill, name and save the worksheet

For grins I have also used the CreateObject method instead of the New designation.

appExcel.quit

This is the conventional way to close the application, but while it closes the visible instance of Excel, a check in Windows Task Manager show that it is still running. And the only way I have found to then subsequently close Excel is through the Task Manager. I've also tinkered with visible properties and closing workbooks and worksheets beforehand to the same result. Having a background instance of Excel running wreaks chaos with further Excel usage.

So I tried closing through an API call

Private sub CallCloseRoutine(strName as string)

'Dim strName as string  'name of created Excel app
Dim bln As Boolean
Dim strClassName As String
Dim strWinName As String
    strWinName = "Microsoft Excel - " & strName
    strClassName = "XLMain"
    bln = fCLoseXL(strClassName, strWinName)
End sub

Private Function fCLoseXL(lpClassName As String, lpWinName As String) As Boolean
Dim lngRet As Long
Dim hWnd As Long

    hWnd = apiFindWindow(lpClassName, lpWinName)
    If (hWnd) Then
       lngRet = apiPostMessage(hWnd, WM_CLOSE, 0, ByVal 0&)
    End If
End Function

Again, the visible copy of Excel closes, but leaves an invisible instance still running. However now when I close the Access application, the Excel instance closes.

The Access app is an A2K version. I have used 2000 and 2003 versions of Excel. And have run this on Win2000NT and WinXP. All with the same results.

This seems to be an issue that has popped up repeatedly (judging from the archives), but I didn't come across any suggestions I haven't already tried nor seen anything to the effect of this being a known bug. Any fresh ideas on how to close the persistant Excel application without closing the Access app?

Thanks,
Bill

 

[Ed2020]

I think you just need a:

Set appExcel=Nothing

at the end of your routine.

Ed Metcalfe.

Please do not feed the trolls.....

 

[JerryKlmns]

The save method is for the workbook, which then needs to be closed. The next step is to quit excel (as you do) and then destroy all object variables.

Dim appExcel As Excel.Application
Dim wkbExcel As Excel.Workbook
Dim wksExcel As Excel.Worksheet
' Create the Excel Application and Workbook
Set appExcel = New Excel.Application
Set wkbExcel = appExcel.Workbooks.Add
Set wksExcel = wkbExcel.Worksheets(1)

'fill, name and save the workbook
wkbExcel.Save
wkbExcel.Close
appExcel.Quit
Set wksExcel = Nothing
Set wkbExcel = Nothing
Set appExcel = Nothing

 

[Golom]

We just had the same issue in another forum.

The problem is an unqualified reference in your code as explained in this MS/KB article

http://support.microsoft.com/kb/q178510/

That unqualified reference doesn't get destroyed when you close a sheet, workbook or application because it is not associated with one of them. You haven't shown all your code so we can't really tell where the unqualified reference might be.

 

[formerTexan]

First, my apologies for leaving out parts of the code (including the "housecleaning" section). I was trying to keep things brief and in this case brevity wasn;t a virtue.

A galaxy of stars to Golom for surmising the unqualified reference and passing on the Microsoft link. It was spot on. And it also cleared up the mystery about why I had previously been able to step through the code and not raise any errors.

Here is the offending line and its replacement:

' ActiveSheet.PageSetup.PrintTitleColumns = vbNullString   
'bad code :<(          wkbExcel.ActiveSheet.PageSetup.PrintTitleColumns = vbNullString   'good code :>)

Thanks to all of you for your time and answers.

Cheers,
Bill