Automation leaves hidden Excel session

[jkfeagle]

I seem to remember coming across a thread about this once but for the life of me I cannot find it now. I am using Automation from Access to create/populate/format/save an Excel file. Everything works great with it. Unfortunately, after I've saved the file, quit the sessions and set my object variables back to nothing, there is still a hidden Excel session running. How oh how do I kill this session? I have even used code I found in the Microsoft Knowledge base for this very purpose and cannot seem to get it to work. One note, closing the database will terminate the hidden Excel session. Here is what I have at the end of my code:

objXLApp.Application.ActiveWorkbook.SaveAs "M:\SafetyRecords.xls"
objXLApp.Application.ActiveWorkbook.Close
objXLApp.Parent.Quit
Set objSheet = Nothing
Set objXLApp = Nothing

Anyone have any ideas?

 

[casey]

Try using...

objXLApp.Quit

w/o the Parent????

Otherwise I can't see anything else...

 

[jkfeagle]

Already tried that. Thanks anyway. Any other ideas anyone?

 

[TheTrainMan]

I had exectly the same problem... every time I shut the computer down it came up with an error msg something like "OLE Automation error, could not access memory at location ......"

The previous poster's suggestion to use .Quit is what did it for me!

I noticed that when I shut down there would be as many error messages as for how many times I'd run the code. If I pressed the button 3 times, there'd be three instances running hidden... whilst all I had to do was put one line of code in (MyExcel.Quit), are you sure you're closing down all instances of Excel?? That is the only thing I can think of if .Quit isn't working for you, that you've got more than one instance of excel open and only closing one of them?

...I could be way of the mark though!!!!

 

[jkfeagle]

Was MyExcel your object variable for the session that you defined or is that a built in name?

 

[jkfeagle]

Here is the starting and ending code as it stands right now. I'm posting it together here in hopes that someone will see the error of my ways. Unfortunately despite numerous variations, the problem remains. Someone HELP please!!!

Dim rs As Recordset
Dim intFieldCount As Integer, intRecordCount As Integer, intRowCount As Integer, intSheetCount As Integer
Dim varCurrentValue As Variant
Dim varCurrentField As Variant
Dim varYearVar As Variant
Dim objXLApp As Object
Dim objSheet As Object
Dim dteYearVar As Variant
Dim dteYearVarWhole As Variant


Set rs = CurrentDb.OpenRecordset("Jan")
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Workbooks.Add
objXLApp.Visible = False
Set objSheet = objXLApp.ActiveWorkbook.Sheets(1)


REST OF CODE HERE

objXLApp.Application.ActiveWorkbook.SaveAs "M:\SafetyRecords.xls"
objXLApp.Application.ActiveWorkbook.Close
rs.Close
Set rs = Nothing
Set objSheet = Nothing
objXLApp.Quit
Set objXLApp = Nothing

 

[George Too]

Hi,
I used to have the same problem 'till I cahnged my code to look like the following:

'------------------------------------
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim rst As Recordset
Dim Msg, BoxTitle, DlgStyle As String

'--- open the workbook
Set objXL = New Excel.Application
objXL.Visible = False
Workbooks.Open Filename:=Me!txtPath

'turn warnings off
DoCmd.SetWarnings False
.
.
.'other code here
.

'Save and close
ActiveWorkbook.Save
ActiveWindow.Close

'turn warnings back on
DoCmd.SetWarnings True
'------------------------------------

That's all. Hope it helps.

 

[TheTrainMan]

In my previous post to this thread I suggested that there might have been another instance of excel running that hadn't been closed down.... but then I remembered that that is nothing like the problem I had! It was only a couple of weeks ago as well! If only I had a good memory!!

My problem was that when using SaveAs, and then closing, the original file is still hanging around in the ether somewhere. I solved it by reopening the original file and closing that, after I'd already closed down the excel app object.... I'll have a look at what George Too has posted, even though my code now works ok, it's probably very messy!!!

Soory wasn't more helpful!!!

 

[TheTrainMan]

Sorry, been a long day...

I opened a file "Something"
Did stuff to it
SaveAs "Something Else"
Close the file
ReOpen "Something"
Close "Something"
Close Excel


obviously I didn't close excel before I reopened the file, as I said in previous post!!

Without:

ReOpen "Something"
Close "Something"

The file was still hanging around and giving me the OLE Automation error. I suspect that because you were opening a file and saving it under a different name that your original file was causing your problems!

As I say, this is probably a messy way of doing it and I'm sure George Too's code is much better!!!!

Anyway, you live and learn!! (at least that's the idea!)

 

[jkfeagle]

Unfortunately George Too's code didn't work either. However your idea intrigues me. I am not opening an existing file but rather opening a new file, adding data and the saving as ... I am wondering if the original empty file might be doing as you say. I'll let you know.