Solved

Access VBA - cannot close instance of Excel object

Posted on 2013-06-14
16
692 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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
 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

777 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