Creating/Quitting Excel Object from Outlook

[MWE]

I am running MSOffice2000, in particular Excel2K and Outlook2K. I am using VBA code from within Outlook to create an Excel object, open a workbook, suck out some data, close the workbook, quit the Excel object, etc. The code works fine from Word, MSProject, PowerPoint, etc., but does not work from Outlook. In particular, the Excel object seems to linger in the background after the instance has been "quit". Relevant code:

Dim xlAppl As Object

Set xlAppl = CreateObject("Excel.Application")

... relevant operations ...

xlAppl.Quit
So either I am doing something wrong or Outlook is not behaving itself. If the former, why does the approach work for other MS appls. If the latter, is this a common problem?

[mvidas]

Hey MWE,

If you have any workbooks open, you have to .close those first. often times thats what leads to the instance being opened, as it might have a "this workbook has not been saved..." dialog open. Since the application is probably hidden, you won't see it and it'll hang there. You also have to set the objects to nothing:Dim xlAppl As Object, xlWB As Object

Set xlAppl = CreateObject("Excel.Application")
Set xlWB = xlAppl.workbooks.Open("blah.xls")
'... relevant operations ...
xlWB.Close True
Set xlWB = Nothing
xlAppl.Quit
Set xlAppl = Nothing

[MWE]

Hey MWE,

If you have any workbooks open, you have to .close those first. often times thats what leads to the instance being opened, as it might have a "this workbook has not been saved..." dialog open. Since the application is probably hidden, you won't see it and it'll hang there. You also have to set the objects to nothing:Dim xlAppl As Object, xlWB As Object

Set xlAppl = CreateObject("Excel.Application")
Set xlWB = xlAppl.workbooks.Open("blah.xls")
'... relevant operations ...
xlWB.Close True
Set xlWB = Nothing
xlAppl.Quit
Set xlAppl = NothingThanks for the prompt reply. I am doing precisely as you suggest, i.e., closing the workbook and setting that object = nothing before I quit the Excel instance and then set that object to nothing.
xlbook.Close SaveChanges:=False
set xlbook = Nothing
xlappl.Quit
set xlappl = Nothing
As mentioned previously, the code works fine in every MS appl except Outlook.

[mvidas]

What version of outlook? I just tried it in the vba for ol2k and it worked correctly:Sub mwe()
Dim xlAppl As Object, xlbook As Object

Set xlAppl = CreateObject("Excel.Application")
Set xlbook = xlAppl.workbooks.Open("C:\frank.xls")
xlbook.sheets(1).Range("A10").Value = "MWE"
xlbook.Close True
Set xlbook = Nothing
xlAppl.Quit
Set xlAppl = Nothing
End Sub
Have you tried setting xlappl.visible=true or anything, see if theres a reason its staying in memory?

[MWE]

What version of outlook? I just tried it in the vba for ol2k and it worked correctly:Sub mwe()
Dim xlAppl As Object, xlbook As Object

Set xlAppl = CreateObject("Excel.Application")
Set xlbook = xlAppl.workbooks.Open("C:\frank.xls")
xlbook.sheets(1).Range("A10").Value = "MWE"
xlbook.Close True
Set xlbook = Nothing
xlAppl.Quit
Set xlAppl = Nothing
End Sub Have you tried setting xlappl.visible=true or anything, see if theres a reason its staying in memory?I found the problem. I do not really understand "why" :dunno, but the code now runs.

In the "relevant code" section, I had a call to a function (xlLastRow) that determines the last populated row in a spreadsheet (I submitted this as a KB a year or so ago). I have used this routine a million times in dozens of applications including Excel, Word, MS Project, Powerpoint, etc. It works in Outlook, but its use appears to trigger some strange phenomena in the Excel object. The first time the parent proc (what you called MWE) is run, things go to conclusion correctly, but the Excel object hangs around regardless of the xlappl.Quit So I toggle to TaskManager, manually stop the Excel process, toggle back to Outlook and rerun the proc. This time, the proc halts with an error in xlLastRow about a file not being available on a remote server (makes absolutely no sense to me). I again manually toggle to TaskManager, stop the Excel process and toggle back to Outlook and rerun the proc. This time it runs to completion but the Excel object is still hanging around.

So I simply moved the code from xlLastRow into the parent proc and everything works fine. When I have a moment, I will revise xlLastRow to accept a worksheet object rather than a worksheet name and see if there is some problem with xlLastRow figuring out that xlbook is the parent of the target worksheet. Since xlbook is the only open worksheet, it seems to me that it should also be the active worksheet, but maybe not.

Stay tuned ...

[mvidas]

Interesting! Definitely keep me posted

[MWE]

Still no real resolution, but another interesting developoment ...

Things are set up such that this proc executes when Outlook starts up. On average, it takes about 15 seconds for execution to complete. However, if I run the proc a 2nd time while in the same session (what the proc actually does is essentially unchanged), it takes about 10 seconds. And each time I run it again, the execution time decreases. After 5 or so times, execution time is down to 2 seconds and never gets any better.

Based on time tracking, the difference is how long it takes to create the Excel object and open the Excel file. I could understand why the 2nd time might be a bit faster, but why the execution time decrease each time :dunno