WIN32OLE -- cannot terminate Excel process

ruby-talk
1

I've read all the forum stuff on getting the Excel process to
terminate. Yet the following code does not do that -- Excel.exe
remains in task manager...

  excel = WIN32OLE::new('excel.Application')
  ewb = excel.workbooks.add( t_path + "TTI-DB-Template.xls")
  ews = ewb.Worksheets.Item( 1)
  ews.cells(1, 1).value = "sdfasd" # comment out to make it work

  ewb.saveas( t_path + "Temptemp3.xls")

  # not sure I need all this, but it can't hurt (or can it?)
  ewb.Close(0)
  excel.Quit()
  WIN32OLE.ole_free( excel)
  WIN32OLE.ole_free( ewb)
  WIN32OLE.ole_free( ews)
  excel = nil
  ews = nil
  ewb = nil
  GC.start

However, if I remove the line: ews.cells(1, 1).value = "sdfasd", then
the Excel process DOES go away.

What'd going on here? How do I get rid of the Excel process if I put
anything into the worksheet/workbook?

Thanks. --David.

2

You could kill the excel process... not sure how clean that is though.

Process.kill('KILL', excel.ProcessID)

···

--
Bryan

On Tue, Aug 12, 2008 at 11:47 AM, djlewis <djlewis@triadic.com> wrote:

I've read all the forum stuff on getting the Excel process to
terminate. Yet the following code does not do that -- Excel.exe
remains in task manager...

excel = WIN32OLE::new('excel.Application')
ewb = excel.workbooks.add( t_path + "TTI-DB-Template.xls")
ews = ewb.Worksheets.Item( 1)
ews.cells(1, 1).value = "sdfasd" # comment out to make it work

ewb.saveas( t_path + "Temptemp3.xls")

# not sure I need all this, but it can't hurt (or can it?)
ewb.Close(0)
excel.Quit()
WIN32OLE.ole_free( excel)
WIN32OLE.ole_free( ewb)
WIN32OLE.ole_free( ews)
excel = nil
ews = nil
ewb = nil
GC.start

However, if I remove the line: ews.cells(1, 1).value = "sdfasd", then
the Excel process DOES go away.

What'd going on here? How do I get rid of the Excel process if I put
anything into the worksheet/workbook?

Thanks. --David.

3

djlewis wrote:

I've read all the forum stuff on getting the Excel process to
terminate. Yet the following code does not do that -- Excel.exe
remains in task manager...

  excel = WIN32OLE::new('excel.Application')
  ewb = excel.workbooks.add( t_path + "TTI-DB-Template.xls")
  ews = ewb.Worksheets.Item( 1)
  ews.cells(1, 1).value = "sdfasd" # comment out to make it work

  ewb.saveas( t_path + "Temptemp3.xls")

  # not sure I need all this, but it can't hurt (or can it?)
  ewb.Close(0)
  excel.Quit()
  WIN32OLE.ole_free( excel)
  WIN32OLE.ole_free( ewb)
  WIN32OLE.ole_free( ews)
  excel = nil
  ews = nil
  ewb = nil
  GC.start

However, if I remove the line: ews.cells(1, 1).value = "sdfasd", then
the Excel process DOES go away.

What'd going on here? How do I get rid of the Excel process if I put
anything into the worksheet/workbook?

Thanks. --David.

Your code runs fine on my machine, up through this point:

    excel = WIN32OLE::new('excel.Application')
    ewb = excel.workbooks.add( t_path + "TTI-DB-Template.xls")
    ews = ewb.Worksheets.Item( 1)
    ews.cells(1, 1).value = "sdfasd" # comment out to make it work
    ewb.saveas( t_path + "Temptemp3.xls")
    ewb.Close(0)
    excel.Quit()

If I left out the remainder of your code, the Excel process terminated
properly.

If, however, I included the rest of your code verbatim...

    WIN32OLE.ole_free( excel)
    WIN32OLE.ole_free( ewb)
    WIN32OLE.ole_free( ews)
    excel = nil
    ews = nil
    ewb = nil
    GC.start

...the Excel process did not terminate properly.

If I modified that code to this...

    ews = nil
    ewb = nil
    excel = nil
    WIN32OLE.ole_free( ews)
    WIN32OLE.ole_free( ewb)
    WIN32OLE.ole_free( excel)
    GC.start

...the Excel process terminated properly.

My suggestions:

A) Set object variables to nil before calling ole_free.
B) Set object variables to nil in the reverse order that they were
created.
C) Don't bother with nil, ole_free, and GC.start if you don't need to.

Hope that helps.

David

···

--
Posted via http://www.ruby-forum.com/\.

4

Yup, thought of that, and will do it if I can't figure out how to
terminate the process cleanly. So, how do I get the ProcessID?

Thanks. --David.

···

On Aug 12, 1:57 pm, Bryan Richardson <btri...@gmail.com> wrote:

You could kill the excel process... not sure how clean that is though.

Process.kill('KILL', excel.ProcessID)

--
Bryan

On Tue, Aug 12, 2008 at 11:47 AM, djlewis <djle...@triadic.com> wrote:
> I've read all the forum stuff on getting the Excel process to
> terminate. Yet the following code does not do that -- Excel.exe
> remains in task manager...

> excel = WIN32OLE::new('excel.Application')
> ewb = excel.workbooks.add( t_path + "TTI-DB-Template.xls")
> ews = ewb.Worksheets.Item( 1)
> ews.cells(1, 1).value = "sdfasd" # comment out to make it work

> ewb.saveas( t_path + "Temptemp3.xls")

> # not sure I need all this, but it can't hurt (or can it?)
> ewb.Close(0)
> excel.Quit()
> WIN32OLE.ole_free( excel)
> WIN32OLE.ole_free( ewb)
> WIN32OLE.ole_free( ews)
> excel = nil
> ews = nil
> ewb = nil
> GC.start

> However, if I remove the line: ews.cells(1, 1).value = "sdfasd", then
> the Excel process DOES go away.

> What'd going on here? How do I get rid of the Excel process if I put
> anything into the worksheet/workbook?

> Thanks. --David.

5

I can see how you can free up object memory with
  ews = nil;
  #...
  GC.start

But I'm not sure what calling ole_free after assigning to nil could
possibly do...
How is
  ews = nil;
  WIN32OLE.ole_free( ews)

any different from:
  WIN32OLE.ole_free( nil)
?

-Adam

···

On 8/12/08, David Mullet <david.mullet@gmail.com> wrote:

If I modified that code to this...

   ews = nil
   ewb = nil
   excel = nil
   WIN32OLE.ole_free( ews)
   WIN32OLE.ole_free( ewb)
   WIN32OLE.ole_free( excel)
   GC.start

...the Excel process terminated properly.

My suggestions:

A) Set object variables to nil before calling ole_free.

6

require 'win32ole'

excel = WIN32OLE.new 'excel.Application'

puts excel.ProcessID

···

--
Bryan

Yup, thought of that, and will do it if I can't figure out how to
terminate the process cleanly. So, how do I get the ProcessID?

Thanks. --David.

7

Thanks for all the suggestions. One of my problems was due to running
in a debugger (NetBeans) and stopping at a breakpoint to look at
processes, and sometimes even just killing execution right there.
That prevented the Excel process from terminating, perhaps because GC
did not finish its job.

Still, during debugging, that's a common occurrence, and can lead to a
lot of Excel processes hanging around. So I think the function simply
to kill all automation instances of Excel is handy to have while
debugging, used, say, at the beginning of the program. Here it is...

def kill_excel_automation_processes
  wmi = WIN32OLE.connect("winmgmts://")
  processes = wmi.ExecQuery("select * from win32_process where
commandline like '%excel.exe\"% /automation %'")
  processes.each do |process|
    Process.kill('KILL', process.ProcessID)
  end
end

I don't know if there's a way to get something called upon manual
termination in the debugger. at_exit { } does not seem to do it in
NetBeans.

···

On Aug 12, 6:31 pm, Adam Shelly <adam.she...@gmail.com> wrote:

On 8/12/08, David Mullet <david.mul...@gmail.com> wrote:

> If I modified that code to this...

> ews = nil
> ewb = nil
> excel = nil
> WIN32OLE.ole_free( ews)
> WIN32OLE.ole_free( ewb)
> WIN32OLE.ole_free( excel)
> GC.start

> ...the Excel process terminated properly.

> My suggestions:

> A) Set object variables to nil before calling ole_free.

I can see how you can free up object memory with
  ews = nil;
  #...
  GC.start

But I'm not sure what calling ole_free after assigning to nil could
possibly do...
How is
  ews = nil;
  WIN32OLE.ole_free( ews)

any different from:
  WIN32OLE.ole_free( nil)
?

-Adam

8

OK, thanks. Process.kill works nicely, if I know the processID. But
excel.ProcessID does not fly. In a casual browse through excel's
ole_methods, I don't see anything that would obviously do the trick.

I could always looks through the process, thus...

  wmi = WIN32OLE.connect("winmgmts://")
  processes = wmi.ExecQuery("select * from win32_process where
commandline like '%excel.exe\"% /automation %'")
  for process in processes do
      Process.kill( 'KILL', process.ProcessID.to_i)
    end

That would kill ~all~ automation instances of Excel, which may be what
I want, but maybe not. So, I still don't see an easy way to find the
specific ProcessID, that is, without doing queries before and after
WIN32OLE.new 'excel.Application' and see what new excel process just
appeared.

--David.

Thanks. --David.

···

On Aug 12, 4:41 pm, Bryan Richardson <btri...@gmail.com> wrote:

require 'win32ole'

excel = WIN32OLE.new 'excel.Application'

puts excel.ProcessID

--
Bryan

> Yup, thought of that, and will do it if I can't figure out how to
> terminate the process cleanly. So, how do I get the ProcessID?

> Thanks. --David.

9

Weird... maybe ProcessID is an application-by-application type thing.

···

On Tue, Aug 12, 2008 at 3:57 PM, djlewis <djlewis@triadic.com> wrote:

On Aug 12, 4:41 pm, Bryan Richardson <btri...@gmail.com> wrote:

require 'win32ole'

excel = WIN32OLE.new 'excel.Application'

puts excel.ProcessID

--
Bryan

> Yup, thought of that, and will do it if I can't figure out how to
> terminate the process cleanly. So, how do I get the ProcessID?

> Thanks. --David.

OK, thanks. Process.kill works nicely, if I know the processID. But
excel.ProcessID does not fly. In a casual browse through excel's
ole_methods, I don't see anything that would obviously do the trick.

I could always looks through the process, thus...

wmi = WIN32OLE.connect("winmgmts://")
processes = wmi.ExecQuery("select * from win32_process where
commandline like '%excel.exe\"% /automation %'")
for process in processes do
     Process.kill( 'KILL', process.ProcessID.to_i)
   end

That would kill ~all~ automation instances of Excel, which may be what
I want, but maybe not. So, I still don't see an easy way to find the
specific ProcessID, that is, without doing queries before and after
WIN32OLE.new 'excel.Application' and see what new excel process just
appeared.

--David.

Thanks. --David.