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]

The key to understanding what was/is actually happening is to step through the entire code.

If YOU had to save the workbook, then an error occurred that precluded the Excel Application Object from Quitting.

Your original SORT code produced that kind of error. It seems that the new SORT code I provided solved that problem, produces no error, and consequently allows the Excel Application Object to Quit.

There were several things that contributed to success in the SORT code. 1) properly referencing ranges to the xlSheet object, 2) referencing the entire column range for each Keyn.

Sorry that it took this many attempts. But...

Hurray!

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

 

[aldi07]

Skip,
After all you did, you certainly do NOT have to be sorry.
Your input has been very helpful, things are clearer to me now, and the problem is solved.
I totally agree with your last word: "Hurray" ! I would also add BRAVO for your patience, and your persistence.
Well done!
Alex

 

[SkipVought]

Thank you.

I once visited your fair province on the Plains of Abraham and vicinity circa 1975. In the 1960s worked on the design of a paper mill for a company in Trois-Riveres, just a bit up river.

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

 

[aldi07]

In the sixties Trois-Rivières must have been a village!
You probably enjoyed the warm and friendly weather in the middle of winter !!
Alex

 

[Andrzejek]

Alex,
Don't you think Skip deserves a Star for his help?
Not because he needs one, but because the star will make this thread 'solved' and it may be helpful to others who may have similar issue.


---- Andy

There is a great need for a sarcasm font.

 

[aldi07]

Hi Andy,
I am not very familiar with the stars process. I think I did give Skip two stars by clicking on "great post".
I am ready to give him much more, he certainly does deserve it.
Do I go through each of his response, and click on "great post"? Is that the way to do it?
Please let me know. Also, let me know how can I see the stars already given.
Thank you.
Alex

 

[Andrzejek]

Click on Great Post! link in the most helpful post from Skip.
I think you can only give one star to Skip for one post. If you give him another star in the same thread, his original, first star will disappear.

"how can I see the stars already given" click on your own name, you will see your profile and stars given / received.


---- Andy

There is a great need for a sarcasm font.

 

[aldi07]

Andy,
I first verified the number of votes given by me.
Then, I gave Skip and yourself stars.
Then, I verified again, and the number of votes given by me did not change.
Could you please check and let me know if Skip and yourself received the stars or not?
I made sure to confirm the stars given, in the dialog box appearing as soon as you give a star.
Thank you.

 

[Andrzejek]

(I see) You gave Skip a star on his post from 31 Aug 18 15:19 (which was a replay to my post, BTW)
I don't know if that was the most relevant/important/most helpful post from him, but... that's your choice.

Since you asked...
I did not get any stars from anybody on this thread, but that's OK with me.



---- Andy

There is a great need for a sarcasm font.