Close Excel Application after saving as Text File

[sriling]

Hi all,

I have a Workbook that imports a large text file, then sorts out the info I want. The info I want is then saved as a new text file. I then import the new text file and do a little more extraction and save the final worksheet as a new Workbook. This is all done in one macro & the Excel application is never closed. The file opened by excel changes from .xls to .txt then finally to .xls.

The problem: After I close Excel, I still have an invisible Excel Application showing in my Task Manager. As a result when I try to delete the .xls saved on my Desktop, it tells me it can't be deleted because it is still in use. I can end this process manually in Task Manager, but I want to be able to do it within my macro.

So here are the relevant parts of my code:

'Import text file & sort
 
'save workbook as text file
ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\" & (Environ("UserName")) & "\Desktop\RScaliper.txt", FileFormat:=xlText _
        , CreateBackup:=False
 
'import the new text file
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Documents and Settings\sriling\Desktop\RScaliper.txt", Destination _
...
...
End With
 
'delete xtra worksheets & save as .xls
Application.DisplayAlerts = False
Sheets(1).Delete
Sheets(1).Delete
Sheets(1).Delete
ActiveWorkbook.SaveAs Filename:= _
    "C:\Documents and Settings\" & (Environ("UserName")) & "\Desktop\RScaliper.xls", FileFormat:=xlNormal _
    , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
 
'delete the new text file - not needed anymore
Kill "C:\Documents and Settings\" & (Environ("UserName")) & "\Desktop\RScaliper.txt"

 

[Ruddles]

Is it as simple as doing an Application.Quit?

 

[sriling]

I tried different placements of Application.Quit
That didn't work out for me. I did find a work around for invisible excel application.

So if anyone can find any use for this, great!
Here's my work around:

'import text file & sort
 
'copy the the first sort and paste into new workbook, save new workbook as text file then close it (leaving only my first .xls open)
ThisWorkbook.ActiveSheet.Range("A1:" & qwe).Cells.Copy
Workbooks.Add
Workbooks(2).Worksheets(1).Paste
Workbooks(2).Activate
ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\" & (Environ("UserName")) & "\Desktop\RScaliper.txt", FileFormat:=xlText _
        , CreateBackup:=False
ActiveWorkbook.Close False, False, False
 
'import the new text file
Sheets("DataSort1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Documents and Settings\sriling\Desktop\RScaliper.txt", Destination _
...
End With
 
'delete xtra worksheets & copy the remaining worksheet into a new workbook (final result .xls). Delete the new text file, then close original .xls, leaving my final result .xls open)
Application.DisplayAlerts = False
Sheets(1).Delete
Sheets(1).Delete
Sheets(1).Delete
Dim wb As Workbook
Worksheets(1).Copy
Set wb = ActiveWorkbook
wb.SaveAs Filename:= _
    "C:\Documents and Settings\" & (Environ("UserName")) & "\Desktop\RScaliper.xls", FileFormat:=xlNormal _
    , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
Kill "C:\Documents and Settings\" & (Environ("UserName")) & "\Desktop\RScaliper.txt"
MsgBox "Ta-dah!"
ThisWorkbook.Close False, False, False
End Sub

 

[sriling]

Nevermind, I spoke too soon. I'll have to investigate further...

 

[sriling]

Apparently I missed something. The Excel Application that shows up in my Task Manager, actually does go away. It takes 5 - 10 seconds after closing Excel for me to able to delete the .xls without the message about the file being in use by another program. So I don't know if there really was anything to work around in the first place.