Closing Excel from Access Macro

  • Thread starter Thread starter ChrisBat
  • Start date Start date
C

ChrisBat

Hi,

I have a macro that is operated by a button on a form. In
brief, this macro is supposed to download a dataset from
an intranet page (in Excel), clean and manipulate the
data, resave the spreadsheet, and then close Excel,
returning back to the Access form that was used to open
the original spreadsheet.
I am having a couple of problems:
(1) The code runs fine for a couple of times, but after a
while, when I try and delete the spreadsheet, it says that
the file is still open; this is not possible, because it
is on my desktop, MS Access has also closed (see below)
and there is no indication of Excel being open when I go
into task manager. HOWEVER, what is open (according to
the Processes tab) is excel.exe and agentsvr.exe. Once I
close these, I can delete the spreadsheet without a
problem. How do I stop this from continuing?
(2) The macro also closes Access instead of just excel -
any ideas how I can close one and keep the other open?
Any help would be appreciated.
Thanks for your time,
Chris
 
Here's a little code snippet I tested with:

Dim o As Object
Set o = CreateObject("Excel.Application")
MsgBox o
MsgBox "Test Me"
o.Quit
Set o = Nothing

when the "test me" msgbox is open, you can launch task manager and see
Excel.exe running. Then, once you hit ok and the code completes, you will
see in task manager that the excel instance has dissapeared.
What you were probably doing before was launching a new Excel process (this
won't show up in the Applications section of the task manager because you
launched it through another process without a main window, but it does show
up in the processes section as you discovered), and then closing Access
without quiting the excel process. Since the Excel.exe instance is its own
process and not a child object of the Access process, it remained alive even
after you closed Access.
 
Back
Top