Solved

Access VBA - cannot close instance of Excel object

Posted on 2013-06-14
16
703 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
16 Comments
 
LVL 51

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 51

Expert Comment

by:Gustav Brock
ID: 39247860
You miss this:

Set Wks = CurrXLS.Worksheets(1)

/gustav
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 51

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 51

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 51

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 51

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

631 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