topic Re: Excel won't die! Task won't go away.... in VBA Forum

Excel won't die! Task won't go away....

Anonymous — Mon, 19 Feb 2007 17:57:54 GMT

Hi everyone, so I found a pretty nasty problem with my little code recently and I can't seem to get rid of it. I can't kill excel.exe out of my task manager until I close AutoCad.
What am I doing wrong?
Here's the description of what I'm doing and if need be I can paste some code.

I populate excel with data gathered from a bunch of drawings.

1. Get the data into an array (thank Jeff), runs sweet.
2. Create excel object if I can't getobject. (add reference to excel library)
3. Open my spreadsheet
4. Populate the cells.
5. Quit, Nothing, and whatever else to excel in order for it to clear itself.

This will still leave an instance of excel in task manager until AutoCad is closed.

This causes a problem for second run of the same command. It is being very annoying to say the least.

Anyone encountered this before?

Thanks,
Viktor.

Re: Excel won't die! Task won't go away....

jbooth — Mon, 19 Feb 2007 19:01:02 GMT

This problem has been around for a while.

Try closing Excel (and removing references) in this order:
1. Ranges
2. Worksheets
3. Workbooks
4. Application

This usually works, however I've seen where the rare case where it doesn't. Personally, if I am only reading data from Excel, I try to use ADO just to avoid this issue. If you have any experience opening a database, this may help.

Set your connection details to:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FILENAME;Extended Properties="Excel 8.0;IMEX=1;HDR=NO"

Where FILENAME is the full path to the excel file. You can then use the following SQL statement to pull all of Sheet1:

"SELECT * FROM [Sheet1$]"

You then simply read each row as a datarecord as if the worksheet it was a table in a database.

It's a difficult concept get a handle on, but it is a way to read data from Excel without using COM interop (and therefore without running an excel application). I would give you some sample code, but it was written in VB.NET so it won't help you.

Good luck.

Re: Excel won't die! Task won't go away....

Anonymous — Mon, 19 Feb 2007 19:26:43 GMT

I think you're pointing directly at my problem, it seems that "ranges" is the pain I'm having.
But to answer your second question, I am not just reading excel file, I populate and save it, i really don't want to close it, but since i was having problems killing it, i decided to at least save it and quit it, that wasn't helping.
BUUUUUUUUUUT, I tested again with a much briefer code and it does kill it, so I was able to get down to this problem:

I set do this
dim row1 as range
and it does work since I have it referenced, but i never set it to 0.
Now how do you people get around doing what I have done? Do row1 needs to be a range, do I DIM it as object and then set it?
Appreciate your help.
Thanks,
Viktor.

Re: Excel won't die! Task won't go away....

arcticad — Mon, 19 Feb 2007 21:35:08 GMT

Private Const WM_CLOSE = &H10
[code]
Function CloseXLS(objExcel As Excel.Application) As Boolean
Dim lngHwnd As Long
lngHwnd = FindWindow("XLMAIN", objExcel.Caption)
Set objExcel = Nothing
On Error Resume Next
SendMessage lngHwnd, WM_CLOSE, 0, 0
If Err = 0 Then CloseXLS = True
On Error GoTo 0
End Function
[/code]

Re: Excel won't die! Task won't go away....

Anonymous — Mon, 19 Feb 2007 21:49:40 GMT

Thanks Articad,
Could you give a little description on what is what? Like what is xlmain?

Re: Excel won't die! Task won't go away....

arcticad — Mon, 19 Feb 2007 23:31:34 GMT

XLMAIN is just the name given to excel.
You won't be given a prompt to save this simply kills the running process for excel.

Be Sure and remove all the references as mentioned above.

You also need this at the top of your module.

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Private Const WM_CLOSE = &H10

Function CloseXLS(objExcel As Excel.Application) As Boolean
Dim lngHwnd As Long
lngHwnd = FindWindow("XLMAIN", objExcel.Caption) ' Get The ID of the Window of the Running Process
Set objExcel = Nothing
On Error Resume Next
SendMessage lngHwnd, WM_CLOSE, 0, 0 ' Tell the Windows API to kill the process
If Err = 0 Then CloseXLS = True
On Error GoTo 0
End Function

Re: Excel won't die! Task won't go away....

Anonymous — Tue, 20 Feb 2007 00:29:22 GMT

The way I've gotten around this is to never set a Variable to a Range
Object. Access it through the Worksheet Object. Since I've been using code
like the following example, I have not seen that problem.

''Portions of the following code adapted from code posted by Randall Rath
Option Explicit

Function IsExcelRunning() As Boolean
Dim objXL As Excel.Application
On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
IsExcelRunning = (Err.Number = 0)
Set objXL = Nothing
Err.Clear
End Function

Public Sub AddData(DwgFullName As String, vData As Variant)
Dim sXlFilNam As String

sXlFilNam = Left(DwgFullName, Len(DwgFullName) - 3) & "xls"
Dim oXL As Excel.Application
Dim oWb As Excel.Workbook
Dim oWs As Excel.Worksheet
Dim blnXLRunning As Boolean
blnXLRunning = IsExcelRunning()
If blnXLRunning Then
Set oXL = GetObject(, "Excel.Application")
Else
Set oXL = CreateObject("Excel.Application")
oXL.Visible = False
oXL.UserControl = False
End If
On Error Resume Next
oXL.Visible = True
Set oWb = oXL.Workbooks.Open(sXlFilNam)
If oWb Is Nothing Then
Set oWb = oXL.Workbooks.Add
oWb.SaveAs (sXlFilNam)
End If

Set oWs = oWb.Worksheets(1)
On Error GoTo 0
oWs.Range("A1:D4").Name = "TEST"
oWs.Range("TEST").ClearContents 'to ensure named range is clear of
previous data
oWs.Range("TEST").Resize(UBound(vData, 1), UBound(vData, 2)).Value = vData
exithere:
Set oWs = Nothing

oWb.Save: oWb.Close
Set oWb = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

Sub testmenow()
Dim tr(4, 4) As Double

AddData ThisDrawing.FullName, tr

End Sub

wrote in message news:5491919@discussion.autodesk.com...
I think you're pointing directly at my problem, it seems that "ranges" is
the pain I'm having.
But to answer your second question, I am not just reading excel file, I
populate and save it, i really don't want to close it, but since i was
having problems killing it, i decided to at least save it and quit it, that
wasn't helping.
BUUUUUUUUUUT, I tested again with a much briefer code and it does kill it,
so I was able to get down to this problem:

I set do this
dim row1 as range
and it does work since I have it referenced, but i never set it to 0.
Now how do you people get around doing what I have done? Do row1 needs to be
a range, do I DIM it as object and then set it?
Appreciate your help.
Thanks,
Viktor.

Re: Excel won't die! Task won't go away....

Anonymous — Tue, 20 Feb 2007 18:34:05 GMT

Here's where my problem was, I think all of you were sort of pointing in the right direction, I was just ignoring the problem though.

Sub TestExcel()
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim row(0 To 3) As Excel.Range

Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set wb = xl.Workbooks.Open(FileName:="J:\Shared Data\AutoCad Support\Forms\Transmittal\transmittal-2007.xls")
Set ws = wb.Worksheets("transmittal")
'---------------problem area---------------------'
ws.Range("b19").Select
Set row(0) = xl.ActiveCell
xl.ActiveCell.Offset(0, 2).Select
Set row(1) = xl.ActiveCell
xl.ActiveCell.Offset(0, 1).Select
Set row(2) = xl.ActiveCell
xl.ActiveCell.Offset(0, 3).Select
Set row(3) = xl.ActiveCell
row(0).Value = "test1"
row(1).Value = "test2"
row(2).Value = "test3"
row(3).Value = "test4"
'---------------problem area---------------------'
wb.SaveAs FileName:="C:\test"
wb.Close False
xl.Quit

Set row(0) = Nothing
Set row(1) = Nothing
Set row(2) = Nothing
Set row(3) = Nothing
Set ws = Nothing
Set wb = Nothing
Set xl = Nothing
End Sub

This works good, and the reason why it wasn't working good is because I was trying to use Activecell out of a worksheet, not out of XL.Activecell, it made no sense at the time for me to try it so I looked for some other answer, but that is really the bogger. Hope this helps someone else in the future.
Thanks to all for your help.
Viktor.

Re: Excel won't die! Task won't go away....

jbooth — Wed, 21 Feb 2007 00:04:44 GMT

Killing the Excel process will only annoy users that run multiple instances of Excel, as you have no idea at runtime which instance of Excel you will get when using FindWindow().

You could hack it by changing the name of the window, but disposing of references as you use them is a better programming practice.

I'm not sure if this works, but try something similar to the following:

With sheet.Range("A1:B5")
...Do stuff with A1-B5...
End With

That MAY make sure the VB engine doesn't keep references to excel objects after you are finished with them.