Excel just won't die!!!!!!!

[Ptrace]

I am exporting data to an Excel sheet. The problem is when I try to quit the Excel application it disappears OK, but the task manager still indicates an instance of it running. This causes excel to crash when I go to either open it directly or run my program again. I have tried numerous different methods including:-

(A)
xlObj.quit
set xlObj = Nothing

(B)
Setting up a seperate blank excel file with the simple functionality of forcing Excel to close through the use of a macro, using application.quit


This is really annoying!!!! please help!

 

[CodeFish]

Hi Ptrace,

This is a Common Problem when not fully releasing Excel Object References or,
Referring to a Cell or Sheet Method without a preceding call to the parent object (i.e. worksheet or workbook Respectively).

see Microsoft knowledge Base:-
Q178510 - Excel Automation Fails Second Time Code Runs

Can you Post more of your Excel Code?


Codefish

 

[MrEGuest]

I ran into the same problem and found that I wasn't instaciating and thus destroying the Excel objects properly. To properly create and destroy an Excel app you must have the following objects declared in code...

These are excerpts from an app I have:

'module level variables in Form; early binding
Private xlApp As New Excel.Application
Private myBook As New Excel.Workbook


'This will open and xls file. Most likely in form load
'Could be wherever like btn click event if user selects file
Set myBook = xlApp.Workbooks.Open(Me.txtXLFilePath.Text)


'local worksheet for range work declared in whatever Sub
Dim xlWorkSheet As Excel.Worksheet
'set the local sheet to the select sheet. (also in same Sub)
Set xlWorkSheet = xlApp.Worksheets(strSheetName)
'explicitly destroy this object (in same Sub)
Set xlWorkSheet = Nothing

'This should kill Excel
Set myBook = Nothing
Set xlApp = Nothing

If you have anymore questions or need further clarification please write back.
Good Luck!
Josh

 

[Foada]

Sounds like you are on the right track with set xlObj = Nothing but you are missing an object somewhere, which in turn leaves Excel running. Its hard to tell you where to start but look for a reference to another sheet etc.

 

[Ptrace]

For what it's worth, here is part of my code:

Dim xlsApp As Excel.Application
Dim xlsBook As Excel.Workbook
Dim xlsSheet As Excel.Worksheet

Set xlsApp = Excel.Application

xlsApp.Visible = False

Set xlsBook = xlsApp.Workbooks.Add
Set xlsSheet = xlsBook.Worksheets.Add

' *********************
' Loading sheet with contents of a recordset
' *********************

strFilename = "C:\directory\filename " & intDay & "-" & intMonth & "-" & intYear & ".xls"

' Save the Worksheet
xlsSheet.SaveAs strFilename

xlsBook.Close

xlsApp.Quit

Set xlsSheet = Nothing
Set xlsBook = Nothing
Set xlsApp = Nothing

 

[softwarescience]

Hello

I've had to do similar and I found this bit of code worked for me.

xlsApp.Quit
xlsApp.Parent.Quit
Set xlsApp = Nothing


Hope this helps

 

[Ptrace]

It's just getting even more strange!!!

As well as including the bit of code given by softwarescience (thanks by the way!) I also noticed that when loading the sheet with the data I was using:

xlsApp.Range("A3&quot.Value = "Surname"

So I changed this to:

xlsSheet.Cells(3, 1).Value = "Surname"

Using this I hoped that it would fit in better given that I was setting xlsSheets = nothing later. Instead of probably having to set the ranges to nothing.

My current situation is that after I run my programme I can rerun it without any problems i.e it produces the correct excel file, however if I try to open the file(s) produced, excel crashes. There is still an instance of excel with the task manager.

 

[CodeFish]

Ptrace,

try releasing your references like:-

Set xlsSheet = Nothing
Set xlsBook = Nothing
xlsApp.Quit
Set xlsApp = Nothing

Codefish

 

[Ptrace]

Got it sussed!!!

As well as changing the method of inserting the actual data into the cells, as mentioned in my last post, I also changed the way I created the new excel application. Previously, I used this method:

Dim xlsApp as New Excel.Application

Set xlsApp = Excel.Application

But then I decided to use a reference to an ActiveX object, by using CreateObject as follows:

Dim xlsApp as Object

Set xlsApp = CreateObject("Excel.Application&quot

By using this, it creates a new instance of the application, and by correctly releasing the other references, xlsBook and xlsSheet, when finished, there are no problems with closing down or re-running the programme.

Thanks again for all the help,
PTrace ;-)