Excel Automation Objects Garbage Collection

Posted on 2012-09-14
Last Modified: 2012-09-14
This is a follow-up question to one that was resolved yesterday (Q_27863723)
To recap:
I am working on an Access 2003 mdb on an XP platform. I have a batch process that imports Excel workbooks into the mdb. For each workbook in a user-specified folder, I call a subroutine which reformats some columns along with some other processing.
I ran into an error where the subroutine would run without error the first time through. On the second iteration, though, it would abend with error 91, Object variable or With block variable not set, on a line with that referred to an ‘unqualified’ Excel Selection object (i.e. “Selection” as opposed to “.Selection”).
I don’t want to get into a discussion about the merits on the usage of an Excel Selection object without qualifying it as a member of another object. I now recognize it is poor form and will avoid the practice in the future.
But, I remained curious as to why it is that the code would work the first time through, but not the second. It suggests to me that there is some loose end out there in the Access heap.
I added some additional code to the Clean up section of the sub; setting the Selection object to nothing and executing a TASKKILL to force the Excel instance out of the stack.
I am now getting some different results. Instead of an error 91, I am now getting an error 462, The remote server machine does not exist or is unavailable.
But here is what I find most intriguing: I recreated the mdb on my laptop which is running Access 2010 (32 bit) on a Windows 7 Ultimate (64 bit) platform (I am unable to connect to Experts-Exchange from my client’s machine). When I run the code on this machine, it doesn’t get any errors!
So, are these ‘loose ends’ a problem that has resolved since Access 2003? Unfortunately, I don’t currently have a box running Access 2007. Is this fixed in Access 2007? What exactly is happening here?
Can anyone offer some insight into this?
I am including a zip file containing an mdb and 3 small workbooks. The app is set up to look for the workbooks in the same folder as the mdb. To execute, open up the module, and run the FormatBooks subroutine.
Please note that although the mdb was created in Access 2003 format, the references are pointing towards Office 14 Object Libraries. You will have to change the references if you are using an earlier version of MS Office.
Here’s the code that is in the module


Option Explicit
Option Compare Database
Private Const mstrModule As String = "basFormatBooks"
Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwprocessid As Long) As Long

Public Sub FormatBooks()
      Const strProcedure As String = "FormatBooks"
      Dim strFolder As String
      Dim db As DAO.Database
      Dim strFile As String
      Dim strBook As String
10    On Error GoTo ErrorHandler
20    DoCmd.SetWarnings False
30    strFolder = CurrentProject.Path & "\"
40    Set db = CurrentDb
50    With db
60       With .OpenRecordset("tblInput")
70          While Not .EOF
80             strBook = .Fields(0)
90             strFile = strFolder & strBook
100            Debug.Print "Starting " & strBook
110            FormatBook strFile
120            Debug.Print "Finished " & strBook
130            .MoveNext
140         Wend
150      End With
160   End With
170   Debug.Print "DONE!"
180   On Error Resume Next
190   db.Close
200   Set db = Nothing
210   On Error GoTo 0
220   Exit Sub

230   Debug.Print strProcedure, Err, Err.Description, Erl
240   Resume ExitFunction
End Sub

Private Sub FormatBook(strFile As String)
      Const strProcedure As String = "FormatBook"
      Dim xlsSheet As Excel.Worksheet
      Dim xlsApp As Excel.Application
      Dim xlsBook As Excel.Workbook
      Dim xlsRange As Excel.Range
      Dim lngPID As Long
10    On Error GoTo ErrorHandler

20    Set xlsApp = New Excel.Application
      ' Get ProcessID for Excel instance
30    GetWindowThreadProcessId xlsApp.hwnd, lngPID

40    With xlsApp
50    Set xlsBook = .Workbooks.Open(strFile, False, False)
60       With xlsBook
70          Set xlsSheet = .Worksheets(1)
80          With xlsSheet
90             Set xlsRange = .Columns("A:A")
100            With xlsRange
110               .Select
120               Selection.NumberFormat = "0"   '<== Error on this line
130            End With          'With xlsRange
140         End With             'With xlsSheet
150      End With                'With xlsBook
160   End With                   'With xlsApp

170   On Error Resume Next
180   xlsBook.Close False
190   Set xlsRange = Nothing
200   Set xlsSheet = Nothing
210   Set xlsBook = Nothing
220   Set Selection = Nothing
230   xlsApp.Quit
240   Set xlsApp = Nothing
250   Shell "TASKKILL /F /pid " & lngPID, vbHide
260   DoEvents
270   Err.Clear
280   Exit Sub

290   DoCmd.Hourglass False
300   Debug.Print strProcedure & " Error: " & Err.Description & " (" & Err & ") on line " & Erl
310   Resume ExitSub
End Sub
Question by:shambalad
    LVL 119

    Expert Comment

    by:Rey Obrero
    test this codes

    Private Sub LoadTPR(strFile As String)
          Const strProcedure As String = "LoadTPR"
          Dim xlObj as Object
          Dim strCsv As String
        On Error GoTo ErrorHandler
        strCsv = CurrentProject.Path & "\TPR.csv"
          ' Delete csv file
        On Error Resume Next
        Kill strCsv
        On Error GoTo ErrorHandler
    	set xlObj=createobject("excel.application") strFile, False, False
    		xlObj.visible=true  ' if you don't want to see the excel application, just comment this line
    			with xlObj
    			    .activeworkbook.saveas strcsv,FileFormat:=6, createbackup:=false
    			end with
    			set xlObj=nothing
       DoCmd.SetWarnings False
       DoCmd.TransferText acImportDelim, "TPR Import Specification", "tblTPR", strCsv
    	DoCmd.SetWarnings True
    		set xlObj=nothing
            exit sub
       DoCmd.Hourglass False
       Debug.Print strProcedure & " Error: " & Err.Description & " (" & Err & ") on line " & Erl
       Resume ErrExit
    End Sub

    Open in new window

    LVL 7

    Author Comment

    That code works (once I added an "On error Resume Next" directly after the ErrExit label). I expected that. But that isn't cogent to the question I'm asking here. I'm trying to figure out why it is that the line "Selection.NumberFormat = 0" (which is line 120 in the FomatBook sub list above in the first post runs OK the first time the sub is executed, but is then hammered for the for rest of that Access instance.
    FWIW, I'm now getting an error on that line on the laptop too (it's these sorts of things that drive me a little crazy). By the way, it is the addition of the TASKKILL command that changes the error from 91 to 462.
    I want to try this as an ACCDB, too, but I've got a hot issue my client just handed me, which I have to turn my attention to.
    LVL 7

    Author Comment

    Just converted the mdb to an ACCDB. Still getting a 462 error on the 2nd iteration.
    LVL 119

    Expert Comment

    by:Rey Obrero
    the line

    "Selection.NumberFormat = 0"

    does not have a valid reference to the object variable that you used in your codes.

    i never used any additional codes to kill the excel automation processing in my applications.

    if you haven't notice i only used one object variable (xlObj)


    try this in your codes, add a dot (.) before Selection

    100            With xlsRange
    110               .Select
    120               .Selection.NumberFormat = "0"   '<== Error on this line
    130            End With          'With xlsRange

    or this one

    100            With xlsRange
    110               .Select
    120               xlsApp.Selection.NumberFormat = "0"   '<== Error on this line
    130            End With          'With xlsRange
    LVL 7

    Author Comment

    This is not the point. The question is why does it work the first time the subroutine is called (and it can be used multiple times in that first call) and not the second time the subroutine is called?
    LVL 119

    Expert Comment

    by:Rey Obrero
    close your access application, any excel application
    open your Task manager
    * in the process Tab, you will not see any Excel.exe and msaccess.exe that is running

    open your Access app (msaccess.exe will show in the ask Manager), then run your codes (Excel.exe will show in the Task manager)
    after your codes run, since you have close and quit xlsApp,  Excel.exe should be gone from the Task manager processes, but that is not the case ;-(

    at this point, try opening any excel file and see what happen...

    LVL 7

    Author Comment

    OK. Perhaps I should restate what the thesis is here.
    'Selection' is an Excel object.
    When the Selection object is first used in an Access instance, it works OK.
    When I am done working with the Excel objects, I attempt to remove them from the stack and heap using various techniques such as the Quit method, setting the pointers to nothing and using the TASKKILL command. I also added a DoEvents to allow Access to perform any tasks it may have been holding in abeyance.
    After taking these steps, it would appear that everything having to do with that instance of Excel has been removed from the Access stack and heap,
    If I try to use the Selection object again in that same Access session, however, it raises an error,
    This suggests to me that I have not been successful in disposing all of the garbage from my previous instance of Excel within that session.
    This question is about how Access implements the Garbage Collector. I know that if I avoid using the Selection object in the way it is coded in the first post, I won’t get the error. But what I want to know is how I can dispose of this Excel object or any other object I’ve created in an Access so that there are no traces at all left behind.
    LVL 119

    Accepted Solution

    <Selection' is an Excel object.>
    <When the Selection object is first used in an Access instance, it works OK.>


    but since it is not qualified with either
              xlsRange, xlsSheet,  xlsBook, xlsApp

    it is ('Selection' object) global to the calling program (access) , this global reference to an excel object forces excel to stay active.

    to avoid this, make sure that every property, method, object within the automated application is referenced through an explicit variable (   xlsRange, xlsSheet,  xlsBook, xlsApp ) declared in the calling program.

    hope this explains the cause of the problem..

    let me repeat,

    i never used any additional codes to kill the excel automation processing in my applications.
    only these
    set xlObj=nothing
    LVL 7

    Author Comment

    OK, I'll buy that. It's a scoping error (global within a private sub) that slips through the cracks of the object model the first time around.
    As an addendum to this (at the risk of appearing to flog a dead horse), the following does work when executed as a module within a workbook:

    Private Sub formatbooks()
          Dim strFile As String
    10    strFile = "C:\Users\Todd\Desktop\Demo1.xlsx"
    20    FormatBook strFile
    30    strFile = "C:\Users\Todd\Desktop\Demo2.xlsx"
    40    FormatBook strFile
    50    strFile = "C:\Users\Todd\Desktop\Demo3.xlsx"
    60    FormatBook strFile
    End Sub

    Private Sub FormatBook(strFile As String)
          Const strProcedure As String = "FormatBook"
          Dim xlsSheet As Excel.Worksheet
          Dim xlsBook As Excel.Workbook
          Dim xlsRange As Excel.Range
    10    On Error GoTo ErrorHandler
    20    Set xlsBook = Application.Workbooks.Open(strFile, False, False)
    30       With xlsBook
    40          Set xlsSheet = .Worksheets(1)
    50          With xlsSheet
    60             Set xlsRange = .Columns("A:A")
    70             With xlsRange
    80                .Select
    90                Selection.NumberFormat = "0"
    100            End With          'With xlsRange
    110         End With             'With xlsSheet
    120      End With                'With xlsBook

    130   On Error Resume Next
    140   xlsBook.Close False
    150   Set xlsRange = Nothing
    160   Set xlsSheet = Nothing
    170   Set xlsBook = Nothing
    180   Set Selection = Nothing
    190   DoEvents
    200   Err.Clear
    210   Exit Sub

    220   Debug.Print strProcedure & " Error: " & Err.Description & " (" & Err & ") on line " & Erl
    230   Resume ExitSub
    End Sub
    LVL 7

    Author Closing Comment

    Thank you for your patience.
    LVL 7

    Author Comment

    BTW - Excel actually is gone from the stack in Task Manager. That is what was throwing me, But as you suggest, and I concur, this is a scoping error, so all bets are off.
    LVL 7

    Author Comment

    I should probably just put this laptop away and call it a night, but...
    If you run the mdb I zipped for this question, you will see that the Excel instance is removed from the list of processes in Task Manager,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now