Excel not closing completely after opeing from Access 3

[hughesai]

Hi all,

I have an Access 97 db which has to upload some data from various Excel 2000 workbooks running on Win 2K. I have some code which opens an instance of Excel (I have the Excel 9.0 object library referenced), opens various recordsets, does some transfer of data, closes all the recordsets and Excel. This is summarised below:

Dim appXL As Excel.Application
Dim sht As Worksheet
Dim db As Database
Dim rst As Recordset
Set appXL = CreateObject("Excel.Application")
For Each sht In Worksheets
If sht.Name <> "Summary" Then
sht.DELETE
End If
Next sht
Set db = CurrentDb
Set rst = db.OpenRecordset("tblTable")

' Do various work to transfer data

Set rst = Nothing
Set db = Nothing
Set sht = Nothing
appXL.Application.quit
Set appXL = Nothing

All this works fine the first time round, but if I try to run this routine, or any of several similar ones which upload different data, the code fails with the error:

1004 - Method 'Worksheets' of object '_Global' failed

This usually occurs in the "For Each sht in Worksheets" loop above, but can happen in other places.

Further investigation with Windows Tack Manager shows that an Excel process is still running. If I close Access, that Excel process also finishes, and when I restart my Access application, I can run it once again. If I close the Excel process in Task Manager without closing Access, I can also run my code one again.

It, therefore, appears that I am not closing Excel correctly through my code.

Various users run these routines, and the order may vary depending on when various files becoe available. I can't therefore, program all the activities to happen in sequence within one application.

Needless to say, the users are getting frustrated.

Does anyone know where I am going wrong with this or have any suggestions?

Aidan Hughes.

 

[CajunCenturion]

Two things come to mind. First, are you using the Workbook object as part of this process? And if so, is it being properly closed and released after you close and release the WorkSheet object?

In your application.quit, I would simply say:
appXL.quit as it is the application that you want to quit, not the application's Application object.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[hughesai]

Thanks CajunCenturion.

The only place I have mentioned workbooks is:
appXL.Workbooks.Open stFile
to open the Excel file.
I have no specific workbook object defined.
Should I be closing "something" here anyway? - I'm just not sure any more!

I changed the application.quit as you suggested, and that has unfortunately not helped - still the same message.


Aidan Hughes.

 

[lespaul]

change this:

appXL.Application.quit

to:

appXL.quit

HTH

Leslie

 

[CajunCenturion]

My general flow for shutting down Excel is as follows, but it includes the workbook object. You would leave out the Workbook section since you're not using that specific object.

If Not (cExl_WrkSheet Is Nothing) Then
   Set cExl_WrkSheet = Nothing
End If
   
'If Not (cExl_WrkBook Is Nothing) Then
'   cExl_WrkBook.Close
'   DoEvents
'   Set cExl_WrkBook = Nothing
'End If
   
If Not (cExl_Appl Is Nothing) Then
   If Not (cExl_Appl.Workbooks Is Nothing) Then
      cExl_Appl.Workbooks.Close
      DoEvents
   End If
   cExl_Appl.Quit
   DoEvents
   Set cExl_Appl = Nothing
End If

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[hughesai]

Lespaul - sorry, I had already tried that - our posts must have crossed in the ether - it didn't work.

CajunCenturion - I have added the "Workbooks.Close" line from your code to be sure that all workbooks are closed (I had specifically closed each file I opened, but I thought there may be a Book1 sort of sheet involved) - this did not work. The only other difference in our approach is that you have DoEvents in a few places - these I also included, but they did not help either.

Since then I have tried decaring my appXL as an Object instead of an Excel.Application to use late binding - this also failed.

I'm stuck here - I thought I understood this bit !

Aidan

 

[RoyVidar]

I've seen some threads where it seems the sequence of closing and releasing (all) objects is OK but it still leaves an instance of Excel open (to be viewed for instance in the task manager). I've never had it happen myself, a setup thing, version, SP???

In this thread, thread705-673915, the End statement is used. The End statement should reliable release the Excel objects. The challenge is, it releases all variables rather reliable, so if you rely on other variables... but as last resort?

Roy-Vidar

 

[hughesai]

YES ! ! !

Absolutely Perfect.
The End statement closed Excel completely and the problem has gone away. As the appXL.quit was the last line of my program anyway, placing an End after it causes me no problems with other variables.

As always it's the simple things in life . . . . . . . . .
A small 3 letter word has solved weeks of frustration.


thanks,


Aidan.

 

[CajunCenturion]

I am not a fan of using the End statement at all for a couple of reasons.

One, it clears the sympton, but it does not solve the problem. Terminating execution with and End statement also hides potential memory leaks, which is quite likely in this case because there is a hanging reference somewhere that has not been properly disposed of.

Second, it can only be used if those cases where the termination (in this case of Excel) is congruent with the termination of the Application. If you still have other processing to do, End is not an option.

problem has gone away

I doubt the problem has gone away, but rather has been replaced by a different problem, which may surface down the road as a resource problem due to a memory leak. You're much better off fixing the problem of why Excel is not properly terminating, than you are of covering up the problem with an End statement.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[RoyVidar]

Slap on my wrist, there, CajunCenturion, and deserved. I do agree wholeheartly with what you're saying. But when one doesn't see anything that hasn't been brought up before...

Here's two links on "not releasing" when using automation that supports referencing as part of the challenge:
1004 error:
PRB: Excel Automation Fails Second Time Code Runs

"with blocks":
XL2000: Automation Doesn't Release Excel Object from Memory

BTW - I really like your "general flow for shutting down Excel". Would you mind a short explanation of the usage of DoEvents in this context?

Roy-Vidar

 

[CajunCenturion]

Wasn't intended as a slap by any means RoyVidar, just a different approach.

Why do I use DoEvents? Because the Excel application is running as a different process and it takes time for Excel to close down, and execute the deconstructors for all the various Excel objects. By inserting the DoEvents, I'm giving Excel a little more time to do its cleanup. For example, if you have several workbooks open, its possible that you issue the Excel.Quit command before all of the workbooks have been completely closed. The DoEvents should help prevent that overlap from taking place.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[RoyVidar]

Thank you!

Should have been able to figure that out myself, but sometimes... Again, thanx!

Roy-Vidar

 

[CajunCenturion]

Based on the links that you provided, one possible cause for this hughesai's problem is the reference to the Workseets property without the qualified reference:

For Each sht In appXL.Worksheets

I would try making that change and see if it makes a difference.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[hughesai]

I'm sorry everyone - I seem to have started a row here ! !

I have to agree with everything said here since my last post. I was happy at that stage because symtoms I had been having for quite a while had disappeared, but you're quite right Cajuncenturion, there was still a hanging reference.

Between you (RoyVidar and CajunCenturion) you have come up with the correct answer. I have modified my code as suggested to add qualified references to every Excel object, and it has indeed worked. I have removed the "End" statements from my code, and I hopefully have a piece of code that gets the result with no memory leaks!!!!!

Thanks again to both of you.

Aidan Hughes.

 

[UHNSTrust]

Sorry for restarting an old thread.

Hughesai, you said you rectified this by 'qualifying' each reference to an excel object. Could you please provide your code of how you have added 'qualified references' to your code as I am having the same problem. I am not sure how to do this (new to automating excel through access).

My code is as follows:

    Dim dbs As DAO.Database
    Dim myQrydef As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim xlApp As Excel.Application
    Dim FileName As String
    Dim i As Integer, x As Integer, z As Integer
    Dim ExcelRunning As Boolean

    DoCmd.Hourglass (True)
    
    'get dates for the report
    gvarStartDate = [Forms]![frmReportDeanesly]![txtStartDate]
    gvarEndDate = [Forms]![frmReportDeanesly]![txtEndDate]
    
    Set dbs = CurrentDb
    Set myQrydef = dbs.QueryDefs("spTeletherapyMonthlyReturns")
        
    myQrydef.sql = "EXEC spTeletherapyMonthlyReturns '" & Format(gvarStartDate, "mm/dd/yy") & "', '" & Format(gvarEndDate, "mm/dd/yy") & "'"
    
    Set rst = myQrydef.OpenRecordset()
    
    rst.MoveLast
    rst.MoveFirst
    
    If rst.RecordCount < 1 Then
        MsgBox "There are no records for the selected dates.  Please try again", vbInformation, "No data for the report"
        DoCmd.Hourglass (False)
        Exit Function
    End If
    
    FileName = “C:\TeletherapyMonthlyReturns.xls"
    
    'Create Excel Sheet
    ExcelRunning = IsExcelRunning()
    If ExcelRunning Then
        Set xlApp = GetObject(, "Excel.Application")
    Else
        Set xlApp = CreateObject("Excel.Application")
    End If
    
With xlApp

        .Workbooks.Open FileName

        .ActiveWorkbook.Sheets("Data").Select

        'delete current content and formating
        .ActiveSheet.Range("A2").Select
        .ActiveSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

        .Selection.ClearContents

        ' Loop through the Microsoft Access field names and create
        ' the Microsoft Excel labels.
        x = 1

        For i = 0 To rst.Fields.count - 1
            .Cells(x, i + 1).Value = rst.Fields(i).Name
        Next i

        .ActiveSheet.Range("A1:H1").Select
        With .Selection.Interior
            .ColorIndex = 15
            .Pattern = xlSolid
        End With

        'Output the fields
        x = 2
        For z = 1 To rst.RecordCount
For i = 1 To rst.Fields.count
                .Cells(x, i) = rst.Fields(i - 1)
            Next
            x = x + 1
            rst.MoveNext
        Next z

        .ActiveSheet.Range("A2").Select
        .ActiveWorkbook.Sheets("Pivot Table").Select

       .ActiveWorkbook.Close True, , False
       .Quit

End With
    
    Set varReturn = Nothing
    Set rst = Nothing
    Set myQrydef = Nothing
    Set dbs = Nothing
    Set xlApp = Nothing
    DoCmd.Hourglass (False)

I understand why it is happening but do not know how to qualify each (excel) reference through my code.

Any help would be appreciated.

Thanks

Jonathan

 

[PHV]

.ActiveSheet.Range([highlight].[/highlight]Selection, [highlight].[/highlight]ActiveCell.SpecialCells(xlLastCell)).Select


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[UHNSTrust]

PH,

Thanks. I looked past this because the code compiled/worked and the excel file was created properly.

Jonathan