Excel remains in memory even after application ends.

[dynasty]

Hi,
I created an Excel application within Access 97.
However, the Excel still remains in the memory after running the following code. This causes Excel sheet(s) not visible if I open another Excel file.

Could anybody help?
Thanks in advance.

dynasty
-------------------------------------------
Private Sub TestExcel
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim MySheet As Excel.Worksheet

'Create object if not exist
Err.Number = 0
On Error GoTo notloaded
Set xlApp = GetObject(, "excel.Application.8&quot
notloaded:
If Err.Number = 429 Then
Set xlApp = CreateObject("excel.Application.8&quot
End If

xlApp.Workbooks.Add
Set xlBook = xlApp.ActiveWorkbook
Set MySheet = xlBook.Worksheets("Sheet1&quot
MySheet.cell(1,1) = "Hello World"
xlBook.SaveAs "C:\MyFolder\Testfile.xls"

'Clean up
Set MySheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub

 

[tcsbiz]

Here's some code I copied from Microsoft's website at

http://support.microsoft.com/default.aspx?scid=kb;en-us;210148

You need to quit the application in order to remove it from memory.

But, if the user has any other Excel spreadsheet open, this will close those too.

I've used this method with Access 2000.

Function ExcelTest()
Dim xlobject As Object, xlsheet As Object

Set xlobject = CreateObject("excel.sheet.5&quot
Set xlsheet = xlobject.Application.activeworkbook.sheets("sheet1&quot

With xlsheet
.range("a1&quot.Value = CDbl(InputBox("Enter 1st Number", _
"Excel Example&quot)
.range("b1&quot.Value = CDbl(InputBox("Enter 2nd Number", _
"Excel Example&quot)

.range("c1&quot.Value = xlsheet.range("a1&quot.Value * _
xlsheet.range("b1&quot.Value

End With

xlsheet.Parent.SaveAs "c:\examples\xltest.xls"
xlobject.Application.Quit
Set xlobject = Nothing

End Function

Tom.

 

[dynasty]

Thanks Tom.
I tried the method you suggested but it did not work for me.
Is it because I am using Access 97 instead of Access 2000?

Regards,
dynasty

 

[tcsbiz]

Sorry,

I missed that you're using 97. I'm so used to working in 2000 that I search for it automatically.

I've been pretty confused lately because I've been bouncing between Access 2000, COBOL, and Java. The mix of work I have has been pretty odd, lately.

Here's code from Microsoft's website for 97. Regardless of which Access one is using, the main idea is that you have to quit the application. The syntax is different but the idea is the same.

http://support.microsoft.com/default.aspx?scid=kb;en-us;145770

Function OLE_To_Excel()
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application&quot
objExcel.Visible = True
objExcel.Quit ' Must not use brackets around Quit.
' If you put brackets around Quit,
' Microsoft Excel will not close.

Set objExcel = Nothing ' optional.
End Function

The first SET, instantiates Excel so Access can use it.
The second SET removes that instance of Excel but does not remove the application from memory. Set objExcel = Nothing is not even necessary for the code to work. But, it is good practice to use it since each instance of Excel uses a bit of memory.

You must use .Quit to remove Excel from memory.

As a follow-on. I use Microsoft's Knowledgebase website a lot for info like this. A lot of people's questions can be answered there pretty quickly. A big problem I have with it though is the examples tend to be simplistic. Usually, I can make a technique I find there work, though.

"It may be just an opinion but it's MY opinion"

Tom.