Win32ole: application.quit() leave process running

Ruby
1

I’m working a lot with Excel lately in Win XP Pro. It seems EXCEL.EXE
continues to run after my script is finished.

I have:

xlapp = WIN32OLE.new(‘excel.application’)
…
xlapp.activeworkbook.close ;
xlapp.quit() ;

I was expecting the .quit() method to end the EXCEL.EXE process.
However, Task Manger tells me it is still running. Matter of fact,
after running the script 10 times, there are 10 instances of EXCEL.EXE
running.

My logic is simple enough that I know I am issuing the .quit(). I’ve
tried the .ole_free method as well, and no joy, even though it returns
“true”. I’ve also tried:

xlapp.sendkeys("%fx") ; # works, but EXCEL.EXE is still running

Ideas?

Thanks, Todd

2

Interesting. If you exit the ruby/IRB instance, the EXCEL process will
close, but not until then.

irb(main):040:0> xlapp = WIN32OLE.new(‘excel.application’)
=> #WIN32OLE:0x2d90308
irb(main):041:0> objWMIService = WIN32OLE.connect
(“winmgmts:\\.\root\cimv2”
=> #WIN32OLE:0x2d8c99c
irb(main):042:0> colProcessList = objWMIService.ExecQuery(“Select * from
Win32_process”)
=> #WIN32OLE:0x2d88d9c
irb(main):043:0> colProcessList.each do |objProcess|
irb(main):044:1* puts [‘Name’, ‘ProcessId’].collect {|i|
objProcess.send(i)}.join(",")
if objProcess.Name == ‘EXCEL.EXE’
irb(main):045:1> end
EXCEL.EXE,3644
=> nil
irb(main):046:0> xlapp.Quit
=> nil
irb(main):050:0> colProcessList = objWMIService.ExecQuery(“Select * from
Win32_process”)
=> #WIN32OLE:0x2d6d844
irb(main):051:0> colProcessList.each do |objProcess|
irb(main):052:1* puts [‘Name’, ‘ProcessId’].collect {|i|
objProcess.send(i)}.join(",")
if objProcess.Name == ‘EXCEL.EXE’
irb(main):053:1> end
EXCEL.EXE,3644
=> nil

3

Yes, after more searching, I found a few threads here that deal with
this same issue. It appears that this is a problem.

I am going to “work around this” with the following code:

xlapp.activeworkbook.close() ;
xlapp.ole_free ;
xlapp = nil ;
GC.start ;

This causes my workbook to close, leaving an “empty” excel session.
When I subsequently manually close (red x) the excel session, it
terminates completely and can no longer be seen in Task Manager.

Ugly, butI would rather do this than leave multiple EXCEL.EXE processes
running. If I leave the xlapp.quit in there, there is no visible
instance of excel to close, and I have to go into Task Manager and end
the process. Which, in my opinion, is uglier.

Thanks, Todd

4

Todd B. wrote:

Yes, after more searching, I found a few threads here that deal with
this same issue. It appears that this is a problem.

I am going to “work around this” with the following code:

xlapp.activeworkbook.close() ;
xlapp.ole_free ;
xlapp = nil ;
GC.start ;

This causes my workbook to close, leaving an “empty” excel session.
When I subsequently manually close (red x) the excel session, it
terminates completely and can no longer be seen in Task Manager.

Ugly, butI would rather do this than leave multiple EXCEL.EXE processes
running. If I leave the xlapp.quit in there, there is no visible
instance of excel to close, and I have to go into Task Manager and end
the process. Which, in my opinion, is uglier.

Thanks, Todd

Use appl.Quit not appl.quit

make sure your workbooks are closed prior to quitting.

I use this code snippet to open the excel application

begin
  excel = WIN32OLE.connect("excel.application")

rescue
excel = WIN32OLE.new(“excel.application”)
end

Thus I connect to a running Excel instance or start a new instance.

5

Bernard K. wrote:

Use appl.Quit not appl.quit

make sure your workbooks are closed prior to quitting.

I use this code snippet to open the excel application

begin
  excel = WIN32OLE.connect("excel.application")

rescue
excel = WIN32OLE.new(“excel.application”)
end

Thus I connect to a running Excel instance or start a new instance.

Hi Bernard.

Why use appl.Quit and not appl.quit? Observations have shown that they
both work just fine, and all of the Excel DOM collections, objects,
properties, methods and enumerations are handled in a case insensitive
manner. Is it to distinguish between possible future additions to the
script my programmers other than myself that might add a #quit method
and/or due to the #missing_method paradign that WIN32OLE uses? Doesn’t
using capital letters for method names go against the ruby syntax rules
for naming conventions? Is it a holdover from VBA? Does your
recommendation apply to the other Excel DOM methods, properties (etc) as
well?

I’ve worked out a workflow that does not involve using quit (see above),
but I am closing my workbooks. Thanks.

Good idea on the “connect”.

And, finally, to learn the methods of a class, like WIN32OLE, I would
typically go interactive (IRB or such) and issue a

WIN32OLE.methods - Object.methods

to get a list of the methods specific to WIN32OLE. I see connect, and a
few others:

[“codepage”, “codepage=”, “connect”, “const_load”, “ole_free”,
“ole_reference_count”, “ole_show_help”]

Where does one get doc on these methods? And, obviously missing here is
#new, but that’s due to my (probably flawed) process of getting method
names, since one can Object.new too.

Todd

6

Todd B. wrote:

Why use appl.Quit and not appl.quit?

While this usually is not a problem (in my experience), I have
encountered situations where an OLE object’s method/property did appear
to be case-sensitive; where the only change I needed to make to the code
was to capitalize the method according to (VB/VBA) docs.

Where does one get doc on these [win32ole] methods?

The Rdoc page for win32ole:

http://www.ruby-doc.org/stdlib/libdoc/win32ole/rdoc/index.html

In the few instances where Ruby’s GC doesn’t seem to properly dispose of
EXCEL.EXE, I can usually resolve the issue by ensuring that I have
released (set to nil) all child objects, in the reverse order of how
they were created, and then running GC. For example:

xl = WIN32OLE.new(‘Excel.Application’)
wb = xl.Workbooks.Add
ws = wb.Worksheets(1)
range = ws.Range(“A2:K100”)
…
wb.Close
xl.Quit
range = nil
ws = nil
wb = nil
xl = nil
GC.start

I hope that helps (but it may not).

David

7

List Rb wrote:

Interesting. If you exit the ruby/IRB instance, the EXCEL process will
close, but not until then.

This also happens when you Ctrl-C Mongrel instance on which the app is
running. Once Mongrel is killed, EXCEL.EXE process also disappears from
Windows Task Manager

8

On Jul 8, 8:19 am, Todd B. [email protected] wrote:

I was expecting the .quit() method to end the EXCEL.EXE process.
Ideas?

Thanks, Todd

–
Posted viahttp://www.ruby-forum.com/.

Set xlapp.Visible = true (at least I think that’s the param for
visibility, it’s been awhile).

It’s probably a modal dialog asking you to save or something
preventing it from closing. You’ll need to dismiss that first
(programmatically of course).

When I used to work with ruby and excel automation, I found it very
helpful to debug with visibility on, set through an environment
variable. So on my workstation I’d set an APP_ENV=development, and on
the server I’d set APP_ENV=production, and then in my code I could
just say: xlapp.Visible = ENV[‘APP_ENV’] == ‘development’.

Good Luck!

9

Hello, sorry for being too late to reply.

In message “Re: win32ole: application.quit() leave process running”
on 07/07/09, Todd B. [email protected] writes:

This causes my workbook to close, leaving an “empty” excel session.
When I subsequently manually close (red x) the excel session, it
terminates completely and can no longer be seen in Task Manager.

Excel process is terminated if only if the xlapp object is GCed.
In the following code, if the ruby clean the xlapp object,
then Excel process is terminated.

xlapp.quit
xlapp = nil
GC.start # (*1)

But unfortunatelly, xlapp is not always GCed in GC.start(*1).
Sometimes xlapp may be GCed and sometimes not.
This is Ruby GC behavior.

If you want to terminate Excel process, use WIN32OLE#ole_free,
or WIN32OLE.ole_free.

xlapp.quit
WIN32OLE.ole_free(xlapp) # or xlapp.ole_free
xlapp = nil
GC.start

Regards
Masaki S.

10

Sam S. wrote:

Set xlapp.Visible = true (at least I think that’s the param for
visibility, it’s been awhile).

It’s probably a modal dialog asking you to save or something
preventing it from closing. You’ll need to dismiss that first
(programmatically of course).

Yes, that’s the right property. I’m running with visibility on, and
there aren’t any outstanding dialogs awaiting a response. Wish it were
that.

Good idea on the env. varaible setup. Sweet!

Todd