Excel Doesn't Quit in MS Access VBA

[maxhugen]

I'm running VBA code in MS Access to populate cells in an Excel spreadsheet. It works AOK, except that the Quit method doesn't work: Windows Task Manager shows an instance of Excel still running under Processes (although not under Applications).

I start Excel with:

Set appExcel = Excel.Application

When finished, I use:

appExcel.Quit
Set appExcel = Nothing

Any suggestions why the Excel process does not close down please?

MTIA

Max Hugen
Australia

 

[maxhugen]

FWIW, I found the problem, my first line should be:

Set appExcel = [b]New[/b] Excel.Application

Max Hugen
Australia

 

[idbr]

Hi Max,

Also, be very careful with referencing. VBA has a handy habit of opening a new reference to Excel whn you fail to properly reference an action in your VBA script. E.g.

set appExcel = New Excel.Application

' do something
appExcel.Open <workbook>

appExcel.Range("A1").Select

' note lack of app reference
Selection.Delete

Will work just fine, but will leave a hanging instance when you issue

appExcel.Quit

Gotchas include things like sorting:

appExcel.Sort key1:= Range("A1:A10")...

will leave a hanging instance too. You need to use

appExcel.Sort key1:= appExcel.Range("A1:A10")...

It's a real pian in the backside sometimes...

Cheers, Iain

 

[kjv1611]

Too true!

--

"If to err is human, then I must be some kind of human!" -Me

 

[maxhugen]

Thanks Iain

Yes, I was aware of that referencing issue.

FWIW, I changed to late binding (which will throw a compile error if there are any unqualified refs), changed appExcel to type Object (etc), and used:

set appExcel = CreateObject("Excel.Application")

Cheers, Max

Max Hugen
Australia