Solved

Access VBA - cannot close instance of Excel object

Posted on 2013-06-14
16
688 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 49

Accepted Solution

by:
Gustav Brock earned 500 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 49

Expert Comment

by:Gustav Brock
ID: 39247860
You miss this:

Set Wks = CurrXLS.Worksheets(1)

/gustav
0
 

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 49

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

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

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 49

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 49

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now