Excel hangs in memory 2

[Swi]

I am using Excel to sort the data once I get it into a spreadsheet since that data I get is formatted inconsistently. My problem is that Excel is not unloading from memory. I have tracked it down to the area in red. If I comment this bit of code out

    With oXLSheet
        .Range("A" & CurPos & ":A" & rs.RecordCount + CurPos).CopyFromRecordset rs
        [red].Range("A" & CurPos & ":" & CL(i) & rs.RecordCount + CurPos).Sort _
        Key1:=Range("A" & CurPos), _
        Order1:=xlAscending, _
        Header:=xlGuess, _
        OrderCustom:=1, _
        MatchCase:=True, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers[/red]
    End With
    Set oXLSheet = Nothing
    oXLBook.SaveAs txtReportFile.Text
    oXLBook.Close SaveChanges:=False
    Set oXLBook = Nothing
    oXLApp.Quit
    Set oXLApp = Nothing

Any ideas? Thanks.

Swi

 

[Swi]

I did not finish my one sentence.

If I comment this bit of code out [blue]it works fine.[/blue]

Swi

 

[HughLerwill]

Maybe (2nd line edit);

.Range("A" & CurPos & ":" & CL(i) & rs.RecordCount + CurPos).Sort _
Key1:=.Range("A" & CurPos), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=True, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

 

[dglienna]

Here's the sequence:

Option Explicit

Public xlAppTemp As Excel.Application
Public xlWorkBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Dim strDate$

Public Sub GenerateReport()
  On Error GoTo ErrHandler
  
  ' Creating Object for Excel File.....
  Set xlAppTemp = New Excel.Application
  
  ' Making it Invisible and non-Interactive.....
  xlAppTemp.Visible = False
  xlAppTemp.DisplayAlerts = False
    ' Opening Template Excel File.....
  Set xlWorkBook = xlAppTemp.Workbooks.Open(App.Path & "\Book1.xls", , False)
  Set xlSheet = xlWorkBook.Sheets(1)

  ' Making Active to Worksheet 1.....
  xlSheet.Activate

  ' I am doing lot of things in it, but to provide you with example
  xlSheet.Cells(15, 1) = "This is my report 1"

  ' Formating Date to attach with new file name.....
  strDate = Format(Date, "yyyy-mm-dd")
  
  ' Saving excel file with new name on different folder.....
  xlWorkBook.SaveAs App.Path & "\Output" & strDate & ".xls"

Cleanup:
  ' Destroying Objects.....
  Set xlSheet = Nothing
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing
'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True
  xlAppTemp.Quit
  Set xlAppTemp = Nothing
  Exit Sub
ErrHandler:
'I presume this section comes after ErrHandler, in which case you will want to close the workbook without changes.
'(save happens just above if no error occurs)
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing

'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True

  xlAppTemp.Quit
  Set xlAppTemp = Nothing
End Sub

Private Sub Command1_Click()
  Call GenerateReport
  Beep
End Sub

-David
2006 & 2007 Microsoft Most Valuable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[Swi]

HughLerwill,

Thank you very much. It is always the small things that get you like the period I missed. Again, thank you.

Swi

 

[Swi]

dglienna,

Thank you for your input as well.

Swi