> Jeff Hunt wrote:
> > I have a file that needs to be regularly reloaded into the database.
> > Each time it comes to us from the department that outputs it, there
> > are extra rows containing "non data" elements before the actual
> > header row and data. I can't have the creating dept remove those
> > lines because the same file goes to other people who need those
> > rows. It was simple enough to create a macro to clear them out in
> > Excel, but I want to have it load in a single step from the
> > database. I'm trying to open it as an Excel.Application and then run
> > the same steps as my Excel macro. Problem is that when the function
> > quits, it is leaving Excel loaded in memory. I've stepped through
> > the code and it's getting all the way through the end, but if I
> > click the Stop button on the code window, THEN Excel quits out of
> > memory. I've seen others that had this problem listed in the
> > forums, but my current code and their "working" code seem to be the
> > same, and when different I have altered mine to copy theirs but it
> > does the same thing. This is the code I'm using:
> >
> > '--------------------------------------------------
> > Public Sub ExcelTest2(strFileName As String)
> > On Error GoTo ErrHandler
> >
> > Dim xl As Excel.Application
> >
> > Set xl = CreateObject("excel.application")
> >
> > xl.Workbooks.Open strFileName
> > xl.DisplayAlerts = False
> > xl.Range("A1").Select
> > xl.Cells.Find(What:="Long/Short", LookAt:=xlWhole).Activate
> >
> > If xl.ActiveCell.Address <> "$A$1" Then
> > xl.ActiveCell.Offset(-1, 0).Select
> > xl.Range(Selection, Cells(1)).Select
> > xl.Selection.EntireRow.Delete
> > xl.Range("A2").Select
> > xl.Selection.EntireRow.Delete
> > End If
> >
> > xl.ActiveWorkbook.Save
> > xl.DisplayAlerts = True
> > xl.ActiveWorkbook.Close
> > xl.Quit
> >
> > Set xl = Nothing
> >
> > ExitSub:
> > Exit Sub
> > ErrHandler:
> > MsgBox "Error #: " & Err.Number & vbCrLf & Err.Description
> > Resume ExitSub
> > End Sub
>
> It's primarily this line
>
> xl.Range(Selection, Cells(1)).Select
>
> that creates this. The Selection and Cells objects are not anchored
> to a parent object, and will probably create an instance of Excel
> in memory.
>
> xl.Range(xl.Selection, xl.Cells(1)).Select
>
>
http://support.microsoft.com/default.aspx?kbid=178510>
> I'm more inclined to also declare and instantiate objects for workbook
> and sheets, too - so instead of
>
> xl.Workbooks.Open strFileName
>
> I'd use
>
> set wr = xl.Workbooks.Open(strFileName)
>
> and refer directly through wr, in stead of the more implicit
> ActiveWorkbook.
>
> --
> Roy-Vidar
>
>
>