Printable View
I'm creating an excel object using:
and then I'm closing it using:Code:Set xlOut = CreateObject("Excel.Application")
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.Code:xlout.Quit
Set xlOut = Nothing
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]
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?
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).
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...
Cheers mate...Code:'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
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.
Howdy Judd. See that line that goes
Well, the problem's right in there. See it?Code:.Range(Cells(3, 5), Cells(10, 5)).HorizontalAlignment = xlRight
My advice to the world - go easy on your nested withs.
That line SHOULD read:
Stupid, huh?Code:.Range(.Cells(3, 5), .Cells(10, 5)).HorizontalAlignment = xlRight