asked on

Access VBA - cannot close instance of Excel object

I have an Access 2007 app that opens an Excel workbook, does some processing, and closes the workbook. However, I cannot get rid of the Excel object.

Dim Xl As Object
Dim CurrXLS As Object
Set Xl = CreateObject("Excel.Application")
Set CurrXLS = Xl.Workbooks.Open(strPath)
...Do some processing
CurrXLS.Close
Xl.Quit (or Xl.Application.Quit - neither works)
Set Xl = Nothing

However, despite the Close and Quit commands the Excel object remains active and does not disappear from Task Manager until the code is stopped.

Does anybody have any suggestions?
Thanks
Dave

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dgmoore1

ASKER

OK - I modified my code to match yours, but the problem remains. The Excel object still remains until I stop the code. Here's what I'm using:

Dim Xl As Excel.Application
Dim CurrXLS As Excel.Workbook
Dim Wks As Excel.Worksheet

Set Xl = New Excel.Application
Set CurrXLS = Xl.Workbooks.Open(strPath)
Set Wks = Xl.Worksheets(1)

...do some processing

CurrXLS.Close False
Set Wks = Nothing
Set CurrXLS = Nothing
Xl.Quit
Set Xl = Nothing

Evidently I'm still missing something, but I don't see what it is.

Thanks

Gustav Brock

You miss this:

Set Wks = CurrXLS.Worksheets(1)

/gustav

dgmoore1

ASKER

I'm not sure I understand your comment -

Set Wks = CurrXLS.Worksheets(1)

is the 6th line in my code example.

dgmoore1

ASKER

Oops - never mind. I see what you mean

dgmoore1

ASKER

OK - I fixed Set Wks = CurrXLS.Worksheets(1) but the Excel object still does not close unti I stop the code

Gustav Brock

Strange. This has always worked for me.

Did you try a reboot before testing?

/gustav

dgmoore1

ASKER

Nope - I'll give that a try. Thanks

dgmoore1

ASKER

Rebooted - no luck

Gustav Brock

What happens if you skip:

...do some processing

/gustav

dgmoore1

ASKER

In my procedure "...do some processing" is actually a few lines of code that traverses the first several rows of data until it finds the word "COST" in column A, then finds the last row containing data, and uses that information to define the range to use with an Access DoCmd.TransferSpreadsheet action. The range is assigned to a variable, Excel is closed, and then the TransferSpreadsheet action occurs using the range variable. There is nothing in the remaining code that would affect closing the Excel object since the latter is closed before the action is called.

I tried skipping all of that code, going directly from opening to closing Excel - the Excel object still remains open.

Gustav Brock

Can you run my initial test function as is?

/gustav

dgmoore1

ASKER

I pasted your function into a new procedure in my moduel and it works correctly. So now I need to figure out why the same code doesn't work in my original procedure. But I now have a place to start.

Thanks

Gustav Brock

OK, that's some kind of relief.

One method is now to add code blocks piece by piece until failure, then study that last piece.

/gustav

dgmoore1

ASKER

That's what I'm doing now - I'll keep you posted. Thanks

dgmoore1

ASKER

Found it!
To find the last row containing data I was using

LastRow = Cells(TotRows, CurrRange.Column).End(xlUp).Row

which returns the correct result, but evidently prevents Excel from closing properly. I replaced this statement with

LastRow = wks.Range("A99999").End(xlUp).Row

which also returns the correct result, but allows Excel to close. I suspect the problem was with the CurrRange range variable - for some reason the variable prevents Excel from closing while the explicit wks.Range(...) does not cause the same problem.

Thanks for your help!
Dave