Excel won't close when opened from Access

[Jeff Hunt]

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

 

[Ron2006]

I do similar things, however this is how I end the process:


es.ActiveWorkbook.Close (False)
es.Application.Quit
Set es = Nothing



Ron

 

[Jeff Hunt]

I hadn't tried the (False) after the Close command but that didn't work. I
have already used both xl.Application.Quit and xl.Quit. Either way, they
still didn't fix it. Thanks for the suggestion though. I thought that
perhaps it was my references, so I changed the ADO to 2.8 (it had been at
2.1) but that didn't change it either.

 

[RoyVidar]

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.

 

[Ken Snell \(MVP\)]

RoyVidar's answer is exactly correct.

 

[Jeff Hunt]

I had already tried adding the xl. where you suggested, but that by itself
did not correct the problem. Your comments about instantiating workbooks and
worksheets got me going in the right direction, though. The link to MS was
helpful, too. I still don't fully understand why the specific need for those
extra lines when the code ran fine referencing everything off of the
application object (well, fine until the end, anyway). Still, I tried it out
and managed to get it working. Here is my final code so anyone else can see:

'------------------------------------------------
Public Function PositionFileCleanup(strFileName As String) As Boolean
On Error GoTo ErrHandler
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlSht As Excel.Worksheet

Set xlApp = CreateObject("Excel.Application") '''create an instance of Excel
Set xlWb = xlApp.Workbooks.Open(strFileName) '''Open the Excel file
Set xlSht = xlWb.Worksheets(1) '''select the first worksheet as active

PositionFileCleanup = True
xlApp.DisplayAlerts = False

xlSht.Range("A1").Select
If xlApp.ActiveCell = "LongShort" Then
PositionFileCleanup = True
GoTo ExitSub '''if true then the file is already processed so quit
End If

xlSht.Cells.Find(What:="Long/Short", LookAt:=xlWhole).Activate

If xlApp.ActiveCell.Address <> "$A$1" Then
xlApp.ActiveCell.Offset(-1, 0).Select
xlSht.Range("A1", xlApp.ActiveCell.Address).Select
xlApp.Selection.EntireRow.Delete
xlSht.Range("A2").Select
xlApp.Selection.EntireRow.Delete
xlSht.Columns("E:E").Select
xlApp.Selection.Find(What:="(", LookAt:=xlPart,
SearchDirection:=xlNext).Activate
xlSht.Range(xlApp.ActiveCell.Address).Select
xlApp.Selection.EntireRow.Delete
xlSht.Columns("B:B").Select
xlApp.Selection.EntireColumn.Delete
xlSht.Range("A1").Select
xlApp.ActiveCell = "LongShort"
xlWb.Save
End If

PositionFileCleanup = True

ExitSub:
xlApp.DisplayAlerts = True
xlWb.Close
xlApp.Quit

Set xlSht = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
Set sysObj = Nothing
Exit Function

ErrHandler:
ErrorLog ("modImports.PositionFileCleanup")
Resume ExitSub

End Function
'------------------------------------------------

Seems like a lot of back and forth between the different Excel parts, but it
does exactly what I need it to. Thanks to everyone who helped, especially
RoyVidar.