Excel Orphans stuck in Memory

[wsthomas1]

I have a VBA application in Access that does a lot of spreadsheet manipulation. At the end of the subroutine or function I close the spreadsheet and set all the objects to Nothing. Looking at task manager, as soon as the appliction object is set to Nothing, the Excel instance clears from memory. However, I wanted to add a chart to the spreadsheet. Although the code created the chart, Excel no longer is released from memory at the end of the function as seen in Task Manager. I've searched quite a few message boards, and I believe I'm doing everything correctly as far as using objects for everything, not using the With construct, and setting the objects equal to nothing in the correct order. Below is some sample code, I removed much of it to keep the listing short.

Any help would be greatly appreciated.

Dim exlApp As Excel.Application
Dim exlBook As Excel.Workbook
Dim exlSheet As Excel.Worksheet

Set exlApp = CreateObject("Excel.Application")
Set exlBook = GetObject("c:\junk.xls")

exlApp.Visible = True
exlBook.Windows(1).Visible = True

Set exlSheet = exlBook.Worksheets(1)
exlBook.Sheets(1).Activate

'***************************************************
'add data and format spreadsheet code here
'***************************************************

Dim exlChart As Excel.Chart
Dim exlRange As Excel.Range

Set exlChart = Charts.Add
Set exlRange = exlSheet.Range("A1:E979")
exlChart.ChartType = xlLine
exlChart.SetSourceData exlRange, xlColumns
exlChart.Location xlLocationAsObject, "Sheet1"

exlBook.Save
exlBook.Close
exlApp.Quit

CleanUpAndExit:

If exlRange Is Nothing Then
Else
Set exlRange = Nothing
End If
If exlChart Is Nothing Then
Else
Set exlChart = Nothing
End If

If exlSheet Is Nothing Then
Else
Set exlSheet = Nothing
End If

If exlBook Is Nothing Then
Else
Set exlBook = Nothing
End If

If exlApp Is Nothing Then
Else
Set exlApp = Nothing
End If

Exit Function

 

[Mr. B]

I recently had the same type of issue when working in Excel from Access. It took a while to trace down the statements that were causing the rogue Excel.exe to remain in memory. The way I traced mine down was to start with just simply opening the Excel application and then immediately run my code that would close the instance of the applicaition. I would then check to see that I had infact been successful in closing Excel and removing it from memory. I would then add one statement and test again. I repeated this process until I found the statement that was causing it to remain in memory. I cannot remember exactly what was causing the issue but it had to do with the way I was refering to an object. When I got that corrected the problem went away.

I wish I could be more explicit as to the solution, but I think you will just have to start with the absolute starting point and test until you find your statment that when you make the statement the application no longer is removed from memory.

 

[boblarson]

It would appear that your offending code is this line (specifically the part in red):

Set exlChart = Charts.Add

You need to tie that to the application object

Set exlChart = exlApp.Charts.Add


Read my article on why this is (this article by me was also posted by the Access Team on the official Microsoft Access Team Blog - back in about September 2010).

 

[wsthomas1]

Hi Bob,

I can't thank you enough. I've spent hours going over the code and searching message boards for the solution. As soon as I added your fix it worked perfectly. Thanks Again!