Desperately seeking Closure

[timl.41444]

The following is dealing with Active X and Excel. I know of only this forum for answers.

My Problem (today) :

I am sending Sketch numbers to Excel to be sorted and then Print the new sorted list.
I can open Excel and plug in my data just fine.

My problem occurs when I try to close the Instance of Excel I just opened. It won't.
I am doing the Ctrl+Alt+Delete and then Task Manager. Under "Processes" is where I can see my Excel.exe start up, do all the massaging, and then it just sits there......mocking me. I can seem to close it.

When I run the macro again...It finds the Instance open from the first try, does its thing. But still won't close. What Gives ??

ANY tips are appreciated
Thank you

Tim Livingston
Acad 2004 vb6

The following code is just a fragment but the Excel code is still here

Option Explicit

Public Sub CoilateTickets()

Dim MyExcel As Excel.Application
Dim MyExcelSheet As Excel.Worksheet
Dim MySS As AcadSelectionSet
Dim GrpC(0 To 1) As Integer
Dim GrpV(0 To 1) As Variant

GrpC(0) = 0: GrpV(0) = "Insert"
GrpC(1) = 2: GrpV(1) = "SPI-Tick*"

Set MySS = ThisDrawing.SelectionSets.Add("SKNumbers95")
MySS.Select acSelectionSetAll, , , GrpC, GrpV
'--------------------------------------------------------
'Go get Excel and open the worksheet
On Error Resume Next
Set MyExcel = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set MyExcel = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could Not Load Excel!", vbExclamation
End
End If
End If
On Error GoTo 0
MyExcel.Workbooks.Add ("I:\CADD\TIM\Auto-Excel Files\SkNumberer.xls")
MyExcel.Visible = True
MyExcel.Sheets("Sheet1").Select
Set MyExcelSheet = MyExcel.ActiveWorkbook.Sheets("Sheet1")
'------------------------------------------------------------------------------
'Populate the worksheet
'------------------------------------------------------------------------------------------
'Sort the column
'------------------------------------------------------------------------------------------
'Print the tickets
'------------------------------------------------------------------------------------------
' Close up Shop
MySS.Delete
MyExcel.DisplayAlerts = False
MyExcel.ActiveWindow.Close False
MyExcel.DisplayAlerts = True
MyExcel.Quit
If Not MyExcel Is Nothing Then
Set MyExcelSheet = Nothing
Set MyExcel = Nothing
End If
End Sub

[Ed Jobe]

It's your 'Close up shop' code. With most object variables, they get reset to Nothing when they go out of scope. But Excel is notorious for leaving objects hang. You have to explicitly set them to Nothing, note, in reverse creation order. For example, you can't create a workbook object until you create an app object. Therefore, you have to reset the workbook first, then the app. Also, be careful because some operations create objects, e.g. a range object. You may not directly create them but you have to handle clearing them. So in your code, you clear the app object first and then do an IF check. That will always cause this problem. Just replace that with a series of Set statements. Set all objs, to Nothing and eliminate the IF.

[timl.41444]

Ed - Thank you for the advise

My new close up shop portion
now looks like this :

' Close up Shop
MySS.Delete
MyExcel.DisplayAlerts = False
MyExcel.ActiveWindow.Close False
MyExcel.DisplayAlerts = True
MyExcel.Quit
Set MyExcel = Nothing
Set MyExcelSheet = Nothing

BUT, Excel is still open
Does my new code match what you meant ?

Thanks again
Tim Livingston

[Ed Jobe]

No, check the last to Set statements. You can't close xl if a sheet is still resident in memory. Reverse the two lines. Also, wheres the workbook obj? Think of a parent-child relationship. The sheet is part of a workbook, which is part of an app. You can have an app with no workbook, but not the other way around. So if the workbook is still open, the app has to be open.

[timl.41444]

Thanks again Ed -

Unfortunatlly I still can't get it to Close.

Below is the code as of now...
Again Thank you.





Option Explicit

Public Sub CoilateTickets()

Dim MyExcel As Excel.Application
Dim MyExcelWorkbook As Excel.Workbook ' added the workbook object
Dim MyExcelSheet As Excel.Worksheet
Dim MySS As AcadSelectionSet
Dim GrpC(0 To 1) As Integer
Dim GrpV(0 To 1) As Variant

GrpC(0) = 0: GrpV(0) = "Insert"
GrpC(1) = 2: GrpV(1) = "SPI-Tick*"

Set MySS = ThisDrawing.SelectionSets.Add("SKNumbers100")
MySS.Select acSelectionSetAll, , , GrpC, GrpV
'--------------------------------------------------------
'Go get Excel and open the worksheet
On Error Resume Next
Set MyExcel = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set MyExcel = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could Not Load Excel!", vbExclamation
End
End If
End If
On Error GoTo 0
'-----------------------------------------------------------------------------------------------------------------
' I have added the workbook object here
Set MyExcelWorkbook = MyExcel.Workbooks.Add("I:\CADD\TIM\Auto-Excel Files\SkNumberer.xls")
MyExcel.Visible = True
MyExcel.Sheets("Sheet1").Select
Set MyExcelSheet = MyExcel.ActiveWorkbook.Sheets("sheet1")
'------------------------------------------------------------------------------
'Populate the worksheet
'------------------------------------------------------------------------------------------
'Sort the column
'------------------------------------------------------------------------------------------
'Print the tickets
'------------------------------------------------------------------------------------------
' Close up Shop
MySS.Delete
MyExcel.DisplayAlerts = False
MyExcel.ActiveWindow.Close False
MyExcel.DisplayAlerts = True
MyExcel.Quit

Set MyExcelSheet = Nothing
Set MyExcelWorkbook = Nothing
Set MyExcel = Nothing


End Sub

[Ed Jobe]

I can't see all you code for what's going on in xl, but you must be creating other objects somewhere else, e.g 'sort the colum'. You've shown me a sheet obj, but I don't see what other code you have that might be working with children of the sheet obj. You have to clear ALL xl objects you create, not just the upper levels. Examine your code for other objects that could still be in memory.

[timl.41444]

Well, Ed,

Looks like I have no other choice....
Here is the whole thing. I hate to keep up with this but I would rather not say how long I have been trying to get this thing going and Insiration has given way to desperation.

FYI, I really hope this isn't the problem but I sometimes get an error when I am trying to "Sort the Column"

Option Explicit

Public Sub CoilateTickets()

Dim MyExcel As Excel.Application
Dim MyExcelWorkbook As Excel.Workbook
Dim MyExcelSheet As Worksheet
Dim MySS As AcadSelectionSet
Dim GrpC(0 To 1) As Integer
Dim GrpV(0 To 1) As Variant

GrpC(0) = 0: GrpV(0) = "Insert"
GrpC(1) = 2: GrpV(1) = "SPI-Tick*"

Set MySS = ThisDrawing.SelectionSets.Add("SKNumbers102")
MySS.Select acSelectionSetAll, , , GrpC, GrpV
'--------------------------------------------------------
'Go get Excel and open the worksheet
On Error Resume Next
Set MyExcel = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set MyExcel = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could Not Load Excel!", vbExclamation
End
End If
End If
On Error GoTo 0
Set MyExcelWorkbook = MyExcel.Workbooks.Add("I:\CADD\TIM\Auto-Excel Files\SkNumberer.xls")
MyExcel.Visible = True
MyExcel.sheets("Sheet1").Select
Set MyExcelSheet = MyExcel.ActiveWorkbook.sheets("sheet1")
'------------------------------------------------------------------------------
'Populate the worksheet
Dim RowNum As Integer
Dim Atts As Variant
Dim SkNumber As String
Dim entity As AcadEntity
RowNum = 1
For Each entity In MySS
Atts = entity.GetAttributes
SkNumber = Atts(0).TextString
MyExcelSheet.Cells(RowNum, "A").Value = SkNumber
RowNum = RowNum + 1
Next
'------------------------------------------------------------------------------------------
'Sort the column
Cells.Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("D1").Select
'------------------------------------------------------------------------------------------
'Print the tickets
Dim i As Integer
Dim J As Integer
Dim CADEnt As AcadEntity
For i = 1 To 1000
If MyExcelSheet.Cells(i, "A").Value = "" Or MyExcelSheet.Cells(i, "A").Value = "zzzzzz" Then
Exit For
Else
For Each CADEnt In MySS
Atts = CADEnt.GetAttributes
If MyExcelSheet.Cells(i, "A").Value = Atts(0).TextString Then
Dim point1 As Variant
Dim point2(0 To 1) As Double
Dim Count As Variant
Dim SelPt As Variant
Dim SelEnt As AcadBlockReference
Dim temp As Variant
point1 = CADEnt.InsertionPoint
ReDim Preserve point1(0 To 1)
For Count = LBound(point1) To UBound(point1)
point2(Count) = point1(Count)
Next Count
point2(0) = point2(0) + 229.22338
point2(1) = point2(1) + 182.5046
ThisDrawing.ActiveLayout.SetWindowToPlot point1, point2
ThisDrawing.ActiveLayout.GetWindowToPlot point1, point2
ThisDrawing.ActiveLayout.PlotType = acWindow
'ThisDrawing.Plot.PlotToDevice
Exit For
End If
Next
End If
Next i
'------------------------------------------------------------------------------------------
' Close up Shop
MySS.Delete
MyExcel.DisplayAlerts = False
MyExcel.ActiveWorkbook.Close False
MyExcel.DisplayAlerts = True
MyExcel.Quit

Set MyExcelSheet = Nothing
Set MyExcelWorkbook = Nothing
Set MyExcel = Nothing


End Sub

[Ed Jobe]

Before I look this over, do me a favor. Perform Tools>Debug and let me know if you have any compile errors.

[timl.41444]

I don't have that option on my menu ( Acad 2004 ) vb editor.

Although I have been using F8 to step through it. Which works fine.

Tim Livingston

[Ed Jobe]

I'm sorry for that brain fog, that should have been Debug>Compile.