Solved

Access VBA - cannot close instance of Excel object

Posted on 2013-06-14
16
689 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

932 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

12 Experts available now in Live!

Get 1:1 Help Now