Closing an excel instance from VBA Access 2

[aldi07]

Hi,
I am using Windows 7 and Office 2007 :
I am instantiating excel from vba access, do some formatting on the excel spreadsheet, than close it.
I realise that the excel instance is still appearing in the task manager.
Here is the code:

*****************************************************************************
Private Sub btnTEST2_Click()
'------------------------------------------------------------
' btnTEST2_Click
'
'------------------------------------------------------------
On Error GoTo btnTEST2_Click_Err

Dim xlObj As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Range

Dim Msg As String
Dim MyRange As String
Dim MyTestExcelClosure As String

Set xlObj = CreateObject("excel.application")
xlObj.Visible = False

MyTestExcelClosure = "C:\Test\MyTestExcelClosure.xlsx"

Set xlWorkbook = xlObj.Workbooks.Open(MyTestExcelClosure)
Set xlSheet = xlObj.ActiveSheet

xlObj.ActiveWorkbook.Names.Add Name:="Catégorie_No", RefersToR1C1:="=Report!R1C3"
xlObj.ActiveWorkbook.Names.Add Name:="Compte_No", RefersToR1C1:="=Report!R1C5"
xlObj.ActiveWorkbook.Names.Add Name:="CompteDescr", RefersToR1C1:="=Report!R1C6"
xlObj.ActiveWorkbook.Names.Add Name:="MontantDate", RefersToR1C1:="=Report!R1C7"

Set xlRange = xlSheet.Cells.Range("a1")
Selection.CurrentRegion.Sort _
key1:=Range("Catégorie_No"), order1:=xlAscending, _
key2:=Range("Compte_No"), order2:=xlAscending, _
key3:=Range("MontantDate"), order2:=xlAscending, _
Header:=xlYes

btnTEST2_Click_Exit:
On Error Resume Next
Set xlRange = Nothing
DoEvents
Set xlSheet = Nothing
DoEvents
Set xlWorkbook = Nothing
DoEvents
xlObj.Quit
Set xlObj = Nothing
DoEvents
Exit Sub

btnTEST2_Click_Err:
MsgBox Error$
Resume btnTEST2_Click_Exit

End Sub
*****************************************************************************

If I remove the sort statement, “Selection.CurrentRegion.Sort _” etc…, then the excel instance disappears from the task manager.
But if I keep the sort statement, there is no way to close that instance of Excel.
There is absolutely no other instance of excel in the task manager before running the code.
Any clues anyone?

Thank you in advance.

Alex

 

[SkipVought]

Private Sub btnTEST2_Click()
'------------------------------------------------------------
' btnTEST2_Click
'
'------------------------------------------------------------
    On Error GoTo btnTEST2_Click_Err
    
    Dim xlObj As Excel.Application
    Dim xlWorkbook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlRange As [b]Excel.[/b]Range
    
    Dim Msg As String
    Dim MyRange As String
    Dim MyTestExcelClosure As String
    
    Set xlObj = CreateObject("excel.application")
    xlObj.Visible = False
    
    MyTestExcelClosure = "C:\Test\MyTestExcelClosure.xlsx"
    
    Set xlWorkbook = xlObj.Workbooks.Open(MyTestExcelClosure)
    Set xlSheet = xlObj.ActiveSheet
    
    [b]xlWorkbook[/b].Names.Add Name:="Catégorie_No", RefersToR1C1:="=Report!R1C3"
    [b]xlWorkbook[/b].Names.Add Name:="Compte_No", RefersToR1C1:="=Report!R1C5"
    [b]xlWorkbook[/b].Names.Add Name:="CompteDescr", RefersToR1C1:="=Report!R1C6"
    [b]xlWorkbook[/b].Names.Add Name:="MontantDate", RefersToR1C1:="=Report!R1C7"
    
    Set xlRange = xlSheet.Cells.Range("a1")
    [b]xlRange[/b].CurrentRegion.Sort _
        key1:=Range("Catégorie_No"), order1:=xlAscending, _
        key2:=Range("Compte_No"), order2:=xlAscending, _
        key3:=Range("MontantDate"), order2:=xlAscending, _
        Header:=xlYes
    
btnTEST2_Click_Exit:
    On Error Resume Next
    Set xlRange = Nothing
    
    Set xlSheet = Nothing
    
    Set xlWorkbook = Nothing
    
    xlObj.Quit
    Set xlObj = Nothing
    
    Exit Sub
    
btnTEST2_Click_Err:
    MsgBox Error$
    Resume btnTEST2_Click_Exit

End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[aldi07]

Hi Skip,
Thank you for your answer. I copied your code as is. Unfortunately, same result:
It does not let me close that instance of excel...

 

[SkipVought]

!) Your SORT was not working for me. I modified your SORT. But maybe your code works in your version.

2) Once your SORT, the open workbook must be saved. THIS is why the Excel Application does not disappear when you Quit. You MUST Save your Workbook before you Quit.

Private Sub btnTEST2_Click()
'------------------------------------------------------------
' btnTEST2_Click
'
'------------------------------------------------------------
    On Error GoTo btnTEST2_Click_Err
    
    Dim xlObj As Excel.Application
    Dim xlWorkbook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlRange As Excel.Range
    
    Dim Msg As String
    Dim MyRange As String
    Dim MyTestExcelClosure As String
    
    Set xlObj = CreateObject("excel.application")
    xlObj.Visible = False
    
    MyTestExcelClosure = "C:\Users\Skip\Downloads\SampleTT.xlsx"
    
    Set xlWorkbook = xlObj.Workbooks.Open(MyTestExcelClosure)
    [b]Set xlSheet = xlWorkbook.Worksheets("Report")[/b]
    
    xlWorkbook.Names.Add Name:="Catégorie_No", RefersToR1C1:="=Report!R1C3"
    xlWorkbook.Names.Add Name:="Compte_No", RefersToR1C1:="=Report!R1C5"
    xlWorkbook.Names.Add Name:="CompteDescr", RefersToR1C1:="=Report!R1C6"
    xlWorkbook.Names.Add Name:="MontantDate", RefersToR1C1:="=Report!R1C7"
    
    Set xlRange = xlSheet.Range("a1")
[b]    xlSheet.Sort.SortFields.Add Key:=xlSheet.Range("Catégorie_No"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    xlSheet.Sort.SortFields.Add Key:=xlSheet.Range("Compte_No"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    xlSheet.Sort.SortFields.Add Key:=xlSheet.Range("MontantDate"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With xlSheet.Sort
        .SetRange xlRange.CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Application.DisplayAlerts = False
    With xlWorkbook
        .Save
        .Close
    End With
    Application.DisplayAlerts = True
[/b]    
btnTEST2_Click_Exit:
    On Error Resume Next
    Set xlRange = Nothing
    
    Set xlSheet = Nothing
    
    Set xlWorkbook = Nothing
    
    xlObj.Quit
    Set xlObj = Nothing
    
    Exit Sub
    
btnTEST2_Click_Err:
    MsgBox Error$
    Resume btnTEST2_Click_Exit

End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Just wondering....

Isn't this approach kind of mixing early and late binding?

Dim xlObj As Excel.Application
...
Set xlObj = CreateObject("excel.application")
xlObj.Visible = True

As to this approach - just the early binding:

Dim xlObj As Excel.Application
...
Set xlObj = New Excel.Application
xlObj.Visible = True

or - even shorter version of it:

Dim xlObj As New Excel.Application
...
xlObj.Visible = True

and that's why it is so difficult to kill Excel at the end


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Andy, the workbook in the Excel instance 1) is not visible, 2) has been changed...and then the code tries to Quit Excel, but can’t cuz there’s a question for the user: “Do you want to save the workbook?” That’s why the instance won’t go away: it’s waiting for an answer.

But you’re right. Shouldn’t be mixing early and late.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

> Isn't this approach kind of mixing early and late binding?

Nope.

 

[Andrzejek]

The [tt]xlObj.Visible = True [/tt] was just my test to see Excel on the screen.

"mixing early and late" is also shown in the naming of the variables: [tt]xlObj[/tt] should be declared [tt]As Object[/tt] (for late binding), but since it is declared as [tt]Excel.Application[/tt] (early binding), should be named something like [tt]xlApp[/tt]

That just me being picky, I guess...


---- Andy

There is a great need for a sarcasm font.

 

[strongm]

Yep, your quibble is with naming conventions, Andy, not the technical reality. There's no mixing of early and late binding of any sort going on here.

 

[Andrzejek]

But you’re right. Shouldn’t be mixing early and late.

> Isn't this approach kind of mixing early and late binding?
Nope.
There's no mixing of early and late binding of any sort going on here.

I am lost - is it or isn't it?


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Shouldn’t because it is confusing and unnecessary.

To get two instances of an object, you’d have to instantiate two SEPARATE objects.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

The OP's code is NOT mixing early and late binding.

(I chose to assume that Skip was giving general advice about mixing, rather than suggesting there was any mixing going on in this specific instance)

 

[Andrzejek]

OK, I've got it now (it is Friday...)
And you are right, I have a "quibble [...] with naming conventions", that's all...


---- Andy

There is a great need for a sarcasm font.

 

[aldi07]

Hi Skip and Andy, thank you for your help.
Yes Andy, the object declaration should be more meaningful, as short as possible, and less messy. I changed it to xlAPP.
Skip, your solution works fine, and the excel instance does disappear from the task manager. However your sort solution gives me unreadable content in the excel file. It is probably due to differen versions of excel. As you know, I use office 2007.
After I run the vba, when I try to open the excel file, it gives me the following message box:

Excel found unreadable content in ‘MyExcelTestClosure.xslx’. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

When I click Yes, it opens the excel file, and displays another message box:

Removed Records: Sorting from /xl/worksheets/sheet2.xml part
Then it gives me a link to click to view log file listing repairs. When I click it, I get:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<recoveryLog xmlns="

http://schemas.openxmlformats.org/s...ror055400_01.xml</logFileName><summary>Errors

were detected in file 'C:\Test\MyTestExcelClosure.xlsx'</summary><removedRecords summary="Following is a list of removed records:"><removedRecord>Removed Records: Sorting from /xl/worksheets/sheet2.xml part</removedRecord></removedRecords></recoveryLog>

HOWEVER:
If I use an xlsm file instead of an xslx, it works fine, and afterwards, I am able to open the xlsm file with no error messages. Curiously, there is no VBA code in that xlsm file…

 

[SkipVought]

Then use your sort code instead of mine.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[aldi07]

Strangely enough, when I use my sort code, excel remains open in the task manager !!
To resolve this problem, I have decided to use your sort code, and apply it to an xlsm file. It doesn't change much for me, and the problem is solved.
Thank you again.

 

[SkipVought]

I used this sort code and it seems to work...

'........
    Set xlRange = xlSheet.Range("a1")
[b]    
    With xlRange.CurrentRegion
        .Sort key1:=xlSheet.Range(xlSheet.Range("Catégorie_No"), xlSheet.Range("Catégorie_No").End(xlDown)), order1:=xlAscending, DataOption1:=xlSortNormal, _
        key2:=xlSheet.Range(xlSheet.Range("Compte_No"), xlSheet.Range("Compte_No").End(xlDown)), order2:=xlAscending, DataOption2:=xlSortNormal, _
        key3:=xlSheet.Range(xlSheet.Range("MontantDate"), xlSheet.Range("MontantDate").End(xlDown)), order3:=xlAscending, DataOption3:=xlSortNormal, _
        Header:=xlYes
    End With
    
[/b]    Application.DisplayAlerts = False
    xlWorkbook.Save
    Application.DisplayAlerts = True

'.......

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[aldi07]

Skip, it worked !!
I don't know why, but it worked...
A bit frustrated because there doesn't seem to be a clear rule on why an excel instance will close or not.
We have to go by trial and error, until it works. Anyway, you certainly found the right syntax.
Thank you very much!

 

[SkipVought]

It CLOSED because you SAVED the SORT changes you made to the workbook before QUITING the Excel Application.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[aldi07]

When you told me to use my sort code instead of yours, I did save my workbook before quiting.
It still did NOT close the excel instance.
But when I replaced my sort by your last sort code, everything went fine. This is what I find strange.
I'm sure there is an explanation, but it is neither simple, nor straight forward...