Excel not closing when opened from VBA code

  • Thread starter Thread starter Ed Finley
  • Start date Start date
E

Ed Finley

I have a VBA routine that uses the Excel object model to open a spreadsheet
and inputs data. If I use the VBA code to close the file I'm OK. When I
leave the file open to work on it outside of Access, then close it from
Excel, something is not completely closing. If I try to run the VBA code
from Access again I get a message that the file is still open and do I want
to open it as read only. In Windows Task Manager Excel is not listed in
open applications, but is in open processes. I can't see any other evidence
of Excel being open. Do I need to "release" it from the VBA code?
Thanks in advance for any help.
Ed
 
When you open EXCEL from VBA code, the default is that EXCEL opens in hidden
mode; thus, you don't see it in the open applications.

If your code leaves "open" (meaning the object isn't closed and set to
Nothing) any object reference to EXCEL application, a workbook file, a
worksheet, a cell or range, or some combination of these, then EXCEL will
continue to run in hidden mode because the objects are still present.

So, if you want to leave EXCEL file open after your code runs, be sure that
you set all objects to Nothing before the code ends.
 
Ken,
Thanks for getting back. I think I've set the object to nothing. If I exit
Access completely and restart it everything works fine, otherwise
something's still open. Here's my code:


Dim oApp As Object
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
On Error Resume Next
oApp.UserControl = True
oApp.DefaultFilePath = "k:\Corky Finley"
With oApp
Workbooks.Open Filename:=StrPhase, Notify:=False
' .Visible = True
' .Activate
Range("A5") = StrStudent
Range("N5") = StrIP
End With
oApp.ActiveWorkbook.SaveAs Filename:=StrFileSaveName, FileFormat:=xlNormal,
_
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


Set oApp = Nothing
 
I vaguely recall that you shoud declare every Excel object that you use.
And set all of them to nothing when done.
Otherwise Excel (or Access) "helps you out" and creates a hidden variable
that is not released when your code is complete.

e.g.
You used Workbooks but not as a variable and you did not set it to Nothing.
 
Also, you need to make quite sure that no unsaved workbooks are open
before you try to shut down Excel. I use code like this (objExcel being
the Excel.Application object):

Do While objExcel.Workbooks.Count > 0
objExcel.Workbooks(1).Close False 'close without saving
Loop
ObjExcel.Quit
Set objExcel = Nothing
 
Joe,
How do I declare workbooks? As a string?
Ed
Joe Fallon said:
I vaguely recall that you shoud declare every Excel object that you use.
And set all of them to nothing when done.
Otherwise Excel (or Access) "helps you out" and creates a hidden variable
that is not released when your code is complete.

e.g.
You used Workbooks but not as a variable and you did not set it to Nothing.
--
Joe Fallon
Access MVP



listed
Click to expand...
 
Something like this:

Dim objXL As Excel.Application

Dim objWBS As Excel.Workbooks

Dim objWB As Excel.Workbook

Dim objWS As Excel.Worksheet
 
I appreciate all the excellent help here and am embarrased to ask for more.
I'm definately not very good with the Excel Object Model. I still can't get
my worksheet opened and saved as a different file name.

Here's my latest code:

Dim oApp As Object
Set oApp = CreateObject("Excel.Application")
Dim ObjWBS As Excel.Workbooks


oApp.Visible = True
On Error Resume Next
With ObjWBS
.Open Filename:=StrPhase, Notify:=False
Range("A5") = StrStudent
Range("N5") = StrIP
End With
oApp.ActiveWorkbook.SaveAs Filename:=StrFileSaveName, FileFormat:=xlNormal,
_
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Set ObjWBS = Nothing
Set oApp = Nothing


Thanks again for any help.
Ed
 
I got it working!!! Thanks for all the help.
Ed


Here's the code that did it:

Dim oApp As Excel.Application
Set oApp = CreateObject("Excel.Application")
Dim ObjWBS As Excel.Workbooks
Set ObjWBS = oApp.Workbooks

With ObjWBS
.Open Filename:=StrPhase, Notify:=False
.Visible = True
.Activate
Range("A5") = StrStudent
Range("N5") = StrIP
End With

oApp.ActiveWorkbook.SaveAs Filename:=StrFileSaveName, FileFormat:=xlNormal,
_
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Set ObjWBS = Nothing
Set oApp = Nothing
 
Fantastic!
Glad to hear that it works!
Nice to know that my tip for declaring all variables helped with the issue
of shutting down the app when done.
 
Back
Top