Waste this dashed object

[coox]

I'm creating an excel object using:

 Set xlOut = CreateObject("Excel.Application")

and then I'm closing it using:

 xlout.Quit
 Set xlOut = Nothing

and the darn thing won't go away until I close my app. Anyone tell me the painfully obvious thing I'm missing? Oh, and I've tried a DoEvents after it, but no joy.

[HeSaidJoe]

Option Explicit

Public oXL As Object

Private Sub Command1_Click()
' Quit Microsoft Excel.
oXL.Quit
'
' Free the object from memory.
Set oXL = Nothing
Command1.Enabled = False
'
End Sub

Private Sub Form_Load()
'this will open Excel
'create and object (Excel SpreadSheet)

Set oXL = CreateObject("Excel.Application")

' Open the workbook that contains the macro to run.
oXL.Workbooks.open "C:\My Documents\try.xls"
'
'as object opens invisible, make visible if needed, if not omit
'the line oXL.visible=true
'
oXL.Visible = True

End Sub

[Edited by HeSaidJoe on 06-13-2000 at 09:50 AM]

[coox]

Hmmm, the problem seems to be getting Excel out of memory. I'm quitting it, and setting the object to nothing, but after all that, when I do ctrl+alt+delete (for the Close Program dialog), it's still sitting there. And when I try to run the report again, I get errors because Excel is already open. But if I quit my app, the excel instance seems to close too. Any ideas anyone?

[Judd]

coox,

Your Excel is retaining an object reference, probably one you didn't explicitly set.

In our case the problem was the use of 'ActiveSheet' to do stuff. Check you processing for any 'ActiveSheet's (i.e. ActiveSheet.Whatever), and get rid of them (use an actual reference like Sheets(1) or something).

[coox]

Judd, thanks, but I replaced the only ActiveSheet with Sheets(1) as suggested, no joy. Here, I'm afraid, is my code - can you see anything obvious? The strange thing is, I'm sure this was working fine a couple of weeks ago...

'Create new workbook, rename sheet
 Set xlOut = CreateObject("Excel.Application")
 With xlOut
      .Application.DisplayAlerts = False
      .Application.Workbooks.Open "C:\Data\T1L.xls"
      .Application.Worksheets(1).Name = "SUN101_" & BatchNo
      For ColLoop = 0 To 13
       .Columns(ColLoop + 2).ColumnWidth = ColWidths(ColLoop)
      Next ColLoop
'Print Data to Sheet
      .Range("B3").Select
      ThisPage = 1
      HeadFoot StartDate, EndDate, ThisPage, TotalPages, BatchNo, "Y"
      ThisPage = ThisPage + 1
'Populate Cells
      Hits = 0
      LateCount = 0
      For WriteLoop = 0 To RecCount
       If WriteLoop > 0 And (WriteLoop / 13) = (WriteLoop \ 13) Then
        .ActiveCell.Offset(6, 0).Range("A1").Select
        HeadFoot StartDate, EndDate, ThisPage, TotalPages, BatchNo, "Y"
        ThisPage = ThisPage + 1
       End If
       .ActiveCell.Cells(1, 1) = Data(WriteLoop, 0) 'Origin City
       .ActiveCell.Cells(1, 2) = Data(WriteLoop, 1) 'Dest City
       .ActiveCell.Cells(1, 3) = Data(WriteLoop, 2) 'HAWB
       .ActiveCell.Cells(1, 4) = Data(WriteLoop, 3) 'A/L
       .ActiveCell.Cells(1, 5) = Data(WriteLoop, 4) 'Actual Weight
       .ActiveCell.Cells(1, 6) = Data(WriteLoop, 5) 'Charge Weight
       .ActiveCell.Cells(1, 7) = Right(Data(WriteLoop, 6), 2) & "/" & _
                                 Mid(Data(WriteLoop, 6), 3, 2) & "/" & _
                                 Left(Data(WriteLoop, 6), 2) 'Rec. at MSAS Date
       .ActiveCell.Cells(2, 7) = Left(Data(WriteLoop, 7), 2) & ":" & _
                                 Right(Data(WriteLoop, 7), 2) 'Rec. at MSAS Time
       .ActiveCell.Cells(1, 8) = Right(Data(WriteLoop, 8), 2) & "/" & _
                                 Mid(Data(WriteLoop, 8), 3, 2) & "/" & _
                                 Left(Data(WriteLoop, 8), 2) 'Delivery Date (Date)
       .ActiveCell.Cells(2, 8) = Left(Data(WriteLoop, 9), 2) & ":" & _
                                 Right(Data(WriteLoop, 9), 2) 'Delivery Date (Time)
       .ActiveCell.Cells(1, 9) = Data(WriteLoop, 10) 'Srvc Level
       .ActiveCell.Cells(1, 10) = Data(WriteLoop, 11) 'Late Y/N
       .ActiveCell.Cells(1, 11) = Data(WriteLoop, 12) 'Hit
       .ActiveCell.Cells(1, 12) = Data(WriteLoop, 13) 'Reason Code
       .ActiveCell.Cells(1, 13) = Data(WriteLoop, 14) 'Comments
       .ActiveCell.Cells(1, 14) = Data(WriteLoop, 15) 'Shipper
       .ActiveCell.Cells(2, 14) = Data(WriteLoop, 16) 'Consignee
 'Tot up Deliver/Hits etc
       If Data(WriteLoop, 11) = "Y" Then LateCount = LateCount + 1
       If Data(WriteLoop, 12) = "Y" Then Hits = Hits + 1
 'Move
       .ActiveCell.Offset(2, 0).Range("A1").Select
      Next WriteLoop
      DeliveredOnTime = TotalShipments - LateCount
 'Get to the Totals page
      .ActiveCell.Offset(((((Round(TotalShipments / 13) * 13) - TotalShipments) * 2) + 6), 0).Select
      HeadFoot StartDate, EndDate, ThisPage, TotalPages, BatchNo, "N"
 'Write Totals page
      Score = Format((TotalShipments - Hits) / TotalShipments, "0.0%")
      With .ActiveCell
           .Cells(3, 2) = "Total Actual Weight:"
           .Cells(3, 5) = Format(TotalActWeight, "#,###,##0.0kg")
           .Cells(4, 2) = "Total Chargeable Weight:"
           .Cells(4, 5) = Format(TotalChgWeight, "#,###,##0.0kg")
           .Cells(6, 2) = "Total No. Shipments:"
           .Cells(6, 5) = TotalShipments
           .Cells(7, 2) = "Total Delivered On Time:"
           .Cells(7, 5) = DeliveredOnTime
           .Cells(8, 2) = "Total Delivered Late:"
           .Cells(8, 5) = LateCount
           .Cells(9, 2) = "Total Hits:"
           .Cells(9, 5) = Hits
           .Cells(10, 2) = "SCORE: "
           .Cells(10, 5) = Score
           .Cells(10, 5).Font.Bold = True
           .Range(Cells(3, 5), Cells(10, 5)).HorizontalAlignment = xlRight
      End With
      PrintKey 'Little sub to fill in the reason code key
 'Sort out Print area
      SetPrintArea = "$B$3:$O$37"
      FirstCell = 3
      For SetLoop = 1 To TotalPages - 1
       FirstCell = FirstCell + 36
       SetPrintArea = SetPrintArea & ",$B$" & (FirstCell) & _
                      ":$O$" & (FirstCell + 34)
      Next SetLoop
      .Sheets(1).PageSetup.PrintArea = SetPrintArea
      .Range("A1").Select
      .ActiveWindow.View = xlPageBreakPreview
      .ActiveWindow.Zoom = 85
      .ActiveWorkbook.SaveAs "C:\Data\Temp.xls"
 End With
 xlOut.Quit
 Set xlOut = Nothing

Cheers mate...

[Judd]

The only thing I can think it might be is all your other 'Active' things. (If they, work in the same way as ActiveSheet, which presumably, they do)

If you know where everything on your spreadsheet is, there shouldn't be any need to use 'Active'.

All I can suggest is you get rid of them all...a pain and I can't guarantee it'll work. They we we 'debugged' our version was to comment out routines, then gradually add them back in, lin by line, until the offending line is found (you add a line, and XL refuses to die...the problem is in that line).

You could try that appraoch.

[coox]

Howdy Judd. See that line that goes

.Range(Cells(3, 5), Cells(10, 5)).HorizontalAlignment = xlRight

Well, the problem's right in there. See it?

My advice to the world - go easy on your nested withs.

That line SHOULD read:

.Range(.Cells(3, 5), .Cells(10, 5)).HorizontalAlignment = xlRight

Stupid, huh?