Closing an Excel instance

[izharmel]

I wrote a code which exports an access table to excel and then manipulates it.

it works OK, but leaves an EXCEL.EXE line in the task bar...
what am I doing wrong?

Sub PrepareExcel(FileNme As String)

Dim xlWb As Excel.Workbook
Dim xlSh As Excel.Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim LastCol As String
Dim LastCell As String

    Set xlWb = Workbooks.Open(CurrentProject.Path & "\Exports\" & FileNme & ".xls")
    Set xlSh = xlWb.Worksheets(FileNme)
    Set Rng = xlSh.Cells
    
   code
.
.
.

Finish:
    xlWb.Application.DisplayAlerts = False     
    xlWb.Save
    xlWb.Application.DisplayAlerts = True
    xlWb.Close        ' doesn't really close anything...
    
    Set xlSh = Nothing
    Set Rng = Nothing
    Set xlWb = Nothing
End Sub

Thanks in advance,

Izhar

[izharmel]

after reading many pages of people with the same problem I realized that the code is relevant to the closing proccess so here's the whole thing

Sub PrepareExcel(FileNme As String)

Dim xlWb As Excel.Workbook
Dim xlSh As Excel.Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim LastCol As String
Dim LastCell As String

    Set xlWb = Workbooks.Open(CurrentProject.Path & "\Exports\" & FileNme & ".xls")
    Set xlSh = xlWb.Worksheets(FileNme)
    Set Rng = xlSh.Cells
        
    ' Find the last occupied row, column and cell using another function
    LastRow = Last(1, Rng)
    LastCol = Chr(Last(2, Rng) + 64)
    LastCell = Last(3, Rng)
    With xlSh
        ' Change the format of the date column to "dd/mm/yyyy hh:mm:ss"
        .Range("A2:A" & LastRow & " ").NumberFormat = "dd/mm/yyyy hh:mm:ss"
    
        'Sort the date column
        .Range("A2:" & LastCell & "").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        
        'set color for the top row
        .Range("A1:" & LastCol & "1").Interior.Color = RGB(172, 137, 243)
        .Rows("2").Select       ' Freeze the Top Row
        ActiveWindow.FreezePanes = True
        .Columns("A:B").EntireColumn.AutoFit     'set column width
    End With
    
Finish:
    Set Rng = Nothing
    Set xlSh = Nothing
    xlWb.Application.DisplayAlerts = False     ' save as T_Average.xls
    xlWb.Save
    xlWb.Application.DisplayAlerts = True
    xlWb.Close
    Set xlWb = Nothing
End Sub

Thanks again,
Izhar

[TheSmileyCoder]

Is this code running from within Access or from within another instance of excel?

I dont see you creating an Excel appplication in your code, thats why I am wondering.

Have you tried:

xlWb.Application.Close

as it is the application you want to close, not the workbook(well maybe its both :O)

[Stewart Ross]

Smiley is right that when you close the current workbook you are not closing the application - it is similar to when you close all active worksheets from within Excel itself, as the very last worksheet closed also closes the current active workbook but leaves Excel running until it is exited from the menu.

You can use the Quit method of the application to close the application and exit Excel (there is no Close method of the Excel Application object, unfortunately):

xlWb.Application.Quit

-Stewart

[TheSmileyCoder]

Yes, of course it should be .Quit. My bad, thank your for pointing that out, Delarna.

[ADezii]

As previously indicated, you need to incorporate the Excel Application Object into your Logic, then properly dispose of it, as in:

Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlSh As Excel.Worksheet

Set xlApp = New Excel.Application

Set xlWb = xlApp.Workbooks.Open("C:\Test\Test.xls")

'...

xlApp.DisplayAlerts = False
xlWb.Save
xlApp.DisplayAlerts = True

xlWb.Close
xlApp.Quit

Set xlWb = Nothing
Set xlApp = Nothing

P.S. - Reference Code Lines 1, 5, 7, 11, 13, 16, 19.

[izharmel]

Doesn't work...

Hi everyone,
Thank you very much for the help,
I tried everything that you told me.
While debugging I saw that xlWb.Close closes the excel rectangle from the task bar but leaves the instance in the task manager.
xlApp.quit doesn't seem to do anything... (that's why I left it out in the first place).
Any thoughts?

Sub PrepareExcel(FileNme As String)

    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook
    Dim xlSh As Excel.Worksheet

    Set xlApp = New Excel.Application
    Set xlWb = Workbooks.Open(CurrentProject.Path & "\Exports\" & FileNme & ".xls")
    xlWb.Application.Visible = True
    Set xlSh = xlWb.Worksheets(FileNme)
    Set Rng = xlSh.Cells
        
    ...
    
Finish:
    Set Rng = Nothing
    Set xlSh = Nothing
    xlApp.DisplayAlerts = False     
    xlWb.Save
    xlApp.DisplayAlerts = True
    xlWb.Close
    Set xlWb = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    
End Sub

[TheSmileyCoder]

If you code fails, it will not properly close the excel instance. Sometimes while debuffing my code, I can end up with 5+ open (and hidden) excel applications. Are you sure the excel application is not a "left-over" from previous attempts?

[izharmel]

What I did was open the task manager while working "step by step" through my code.
The excel instance only appears when it is opened in this function.
The code reaches the end without any breaks, so i assume all the rest is OK...

[matrixtushar]

hi,

the workbook object wb.Close takes arguments. try wb.Close(false, Type.Missing,Ty pe.Missing);

This will work.