Excel not quitting in code

[rkasnick]

Hi all, this is driving me crazy. The following routine is called to open a workbook, find two sheets, import them into a table and close the workbook.

Here's the problem, everything works EXCEPT closing Excel. Checking the task manager after this routine exits there is an instance of Excel still running and if I try to open the just closed workbook, I get an error that says it is still in use and open as read-only.

Anybody have any suggestions?

Sub createfile(filename1 As String, account As String)
On Error GoTo Close_Error

foldername = "c:\personal\DEV_MS ACCESS\forecast project\files"
a = foldername & "\" & filename1
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(a)
Set colWorksheets = objWorkbook.Worksheets

'this routine imports the worksheets into tables
For Each objworksheet In colWorksheets
If objworksheet.Visible Then
If objworksheet.Name = "Data Results" Then
DoCmd.TransferSpreadsheet acImport, 8, account & " " & objworksheet.Name, a, False
End If
If objworksheet.Name = "Non-Proforma Revenue" Then
DoCmd.TransferSpreadsheet acImport, 8, account & " " & objworksheet.Name, a, False
End If
End If
Next

'close up everything and exit
objWorkbook.RunAutoMacros xlAutoClose
objWorkbook.Close SaveChanges:=False
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing

Exit Sub
Close_Error:
MsgBox Str(Err) & " " & Error()
Resume Next
End Sub

 

[HarleyQuinn]

I'd say it's because you need to dispose of colWorksheets, preferably before closing and disposing of the workbook object.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[cjashwell]

I had the same problem.

My solution was to add in a reference to the Excel object library and then declare the Excel objects. I can then close the Excel process by closing the Excel objects and releasing the variables:

Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(strFileName)
Set wks = appExcel.Worksheets(1)
[do stuff ... ]

appExcel.ActiveWorkbook.Save
appExcel.ActiveWorkbook.Close
appExcel.Quit

Set wks = Nothing
Set wbk = Nothing
Set appExcel = Nothing

 

[PHV]

What about this ?

Sub createfile(filename1 As String, account As String)
foldername = "c:\personal\DEV_MS ACCESS\forecast project\files"
a = foldername & "\" & filename1
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(a)
For Each objworksheet In objWorkbook.Worksheets
  If objworksheet.Visible Then
    If objworksheet.Name = "Data Results" Or objworksheet.Name = "Non-Proforma Revenue" Then
      DoCmd.TransferSpreadsheet acImport, 8, account & " " & objworksheet.Name, a, False
    End If
  End If
  DoEvents
Next
objWorkbook.Close SaveChanges:=False
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

rkasnick,

PHV's example should work the best, and here's why: In general, you want to keep things in order. For instance, you don't create a recordset of a database before creating the database... you don't create a worksheet before creating a workbook, and you don't create a workbook before you create an Excel application (if you're running in Access VBA rather than Excel - in Excel, the application is already running).

So, when you close everything, you want to go in the opposite order.... for Excel objects... close any worksheet objects, then workbooks, then application.. For Access, close any range objects if you have any, any fields, then forms, tables, queries, etc.. and then close the database object(s)..

That will make sure everything is closed and done away with.

One other reason is that if you open a Workbook in code, it's not adviseable to save the changes unless you specifically are making changes you want to save.

--

"If to err is human, then I must be some kind of human!" -Me

 

[rkasnick]

Thank you all for your answers, I applied PVHs solution first and it did close Excel properly, so that is the one I'm going with.

Problem solved and I appreciate the help....