Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access VBA - cannot close instance of Excel object

Posted on 2013-06-14
16
Medium Priority
?
711 Views
Last Modified: 2013-06-15
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
0
Comment
Question by:dgmoore1
  • 10
  • 6
16 Comments
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 39247454
Here's the order to open and close Excel:

    Dim xls     As Excel.Application
    Dim wkb     As Excel.Workbook
    Dim wks     As Excel.Worksheet
   
    Set xls = New Excel.Application
    Set wkb = xls.Workbooks.Open("c:\test\workbook1.xlsx")
    Set wks = wkb.Worksheets(1)
   
    wks.Name = "My New Name"
    wkb.Close True
   
    Set wks = Nothing
    Set wkb = Nothing
   
    xls.Quit
   
    Set xls = Nothing

You must be very specific to this. If you set your xl to Nothing, its feet are kicked away.

/gustav
0
 

Author Comment

by:dgmoore1
ID: 39247836
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
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39247860
You miss this:

Set Wks = CurrXLS.Worksheets(1)

/gustav
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:dgmoore1
ID: 39247897
I'm not sure I understand your comment -

Set Wks = CurrXLS.Worksheets(1)

is the 6th line in my code example.
0
 

Author Comment

by:dgmoore1
ID: 39247900
Oops - never mind. I see what you mean
0
 

Author Comment

by:dgmoore1
ID: 39247911
OK - I fixed Set Wks = CurrXLS.Worksheets(1) but the Excel object still does not close unti I stop the code
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39248051
Strange. This has always worked for me.

Did you try a reboot before testing?

/gustav
0
 

Author Comment

by:dgmoore1
ID: 39248080
Nope - I'll give that a try. Thanks
0
 

Author Comment

by:dgmoore1
ID: 39248398
Rebooted - no luck
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39250069
What happens if you skip:

...do some processing

/gustav
0
 

Author Comment

by:dgmoore1
ID: 39250251
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.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39250272
Can you run my initial test function as is?

/gustav
0
 

Author Comment

by:dgmoore1
ID: 39250357
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
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39250365
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
0
 

Author Comment

by:dgmoore1
ID: 39250370
That's what I'm doing now - I'll keep you posted. Thanks
0
 

Author Closing Comment

by:dgmoore1
ID: 39250404
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question