Using Win32OLE with Excel leaves Excel process hanging 
Author Message
 Using Win32OLE with Excel leaves Excel process hanging

I have created a neat little ruby program that generates a useful
graph in Excel.  I keep the program on my desktop and doubleclick it
whenever I want to see the graph.  Unfortunately, once I invoke this
program, it seems to leave a process running in the system.  After
20min or so, it will stop working.  What's more, it often prevents
other OLE operations from working in other applications.  It can be
fixed by a logout/logon.  I'm using windows 2000.

Any ideas?

Here is the code for AmazonAnalyzer.rb

require 'AmazonAnalyzer'
require 'win32ole'

if __FILE__ == $0
  periodLength = 7
  if (ARGV[0] != nil) && (ARGV[0].to_i > 0)
    periodLength = ARGV[0].to_i
  end

  puts "Running Amazon Analyzer with period length #{periodLength}"

  analyzer = AmazonAnalyzer.new()
  analyzer.analyzeFile("bookData", periodLength)

  excel = WIN32OLE.new("excel.application")
  workbook = excel.Workbooks.Add()

  col = 'b'
  row = "1"
  analyzer.startDate.step(analyzer.endDate,periodLength) {|date|
    excel.Range(col+row).value = date.to_s
    col = col.succ
  }

  isbns = analyzer.isbns
  isbns.each {|isbn|
    col='a'
    row = row.succ
    title = analyzer.getTitleFor(isbn)
    title = title.split(':')[0]
    title = title.split('(')[0]
    title = title[0,40]
    excel.Range(col+row).value = title

    periods = analyzer.getPeriodsForIsbn(isbn)
    0.upto(periods.max) {|period|
      col = col.succ
      if periods.include?(period)
        excel.Range(col+row).value =
          analyzer.getRange(isbn,period).mean
      end
    }
  }
  xlLine=4
  xlValue=2
  xlRows=1
  xlScaleLogarithmic = -4133
  excel.Range("a1:"+col+row).Select()
  excelChart = workbook.Charts.Add()
  excelChart.type = xlLine
  excelChart.plotBy=xlRows
  excelChart.Legend.Font.Size=8
  excelChart.Axes(xlValue).scaleType = xlScaleLogarithmic
  excelChart.Axes(xlValue).minimumScale = 100

  workbook.saved = true
  excel.visible = true
end

Robert C. Martin  | "Uncle Bob"                  

PO Box 5757       | Tel: (800) 338-6716        
565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
Suite 135         |                               | www.XProgramming.com
Vernon Hills, IL, | Training and Mentoring        | www.junit.org
60061             | OO, XP, Java, C++, python     |

"One of the great commandments of science is:
    'Mistrust arguments from authority.'" -- Carl Sagan



Wed, 11 Aug 2004 22:07:55 GMT  
 Using Win32OLE with Excel leaves Excel process hanging
Have you tried excel.Quit()?

Quote:

> I have created a neat little ruby program that generates a useful
> graph in Excel.  I keep the program on my desktop and doubleclick it
> whenever I want to see the graph.  Unfortunately, once I invoke this
> program, it seems to leave a process running in the system.  After
> 20min or so, it will stop working.  What's more, it often prevents
> other OLE operations from working in other applications.  It can be
> fixed by a logout/logon.  I'm using windows 2000.

> Any ideas?

> Here is the code for AmazonAnalyzer.rb

> require 'AmazonAnalyzer'
> require 'win32ole'

> if __FILE__ == $0
>   periodLength = 7
>   if (ARGV[0] != nil) && (ARGV[0].to_i > 0)
>     periodLength = ARGV[0].to_i
>   end

>   puts "Running Amazon Analyzer with period length #{periodLength}"

>   analyzer = AmazonAnalyzer.new()
>   analyzer.analyzeFile("bookData", periodLength)

>   excel = WIN32OLE.new("excel.application")
>   workbook = excel.Workbooks.Add()

>   col = 'b'
>   row = "1"
>   analyzer.startDate.step(analyzer.endDate,periodLength) {|date|
>     excel.Range(col+row).value = date.to_s
>     col = col.succ
>   }

>   isbns = analyzer.isbns
>   isbns.each {|isbn|
>     col='a'
>     row = row.succ
>     title = analyzer.getTitleFor(isbn)
>     title = title.split(':')[0]
>     title = title.split('(')[0]
>     title = title[0,40]
>     excel.Range(col+row).value = title

>     periods = analyzer.getPeriodsForIsbn(isbn)
>     0.upto(periods.max) {|period|
>       col = col.succ
>       if periods.include?(period)
>         excel.Range(col+row).value =
>           analyzer.getRange(isbn,period).mean
>       end
>     }
>   }
>   xlLine=4
>   xlValue=2
>   xlRows=1
>   xlScaleLogarithmic = -4133
>   excel.Range("a1:"+col+row).Select()
>   excelChart = workbook.Charts.Add()
>   excelChart.type = xlLine
>   excelChart.plotBy=xlRows
>   excelChart.Legend.Font.Size=8
>   excelChart.Axes(xlValue).scaleType = xlScaleLogarithmic
>   excelChart.Axes(xlValue).minimumScale = 100

>   workbook.saved = true
>   excel.visible = true
> end

> Robert C. Martin  | "Uncle Bob"

> PO Box 5757       | Tel: (800) 338-6716
> 565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
> Suite 135         |                               | www.XProgramming.com
> Vernon Hills, IL, | Training and Mentoring        | www.junit.org
> 60061             | OO, XP, Java, C++, Python     |

> "One of the great commandments of science is:
>     'Mistrust arguments from authority.'" -- Carl Sagan

--
Alexander Schofield


Wed, 11 Aug 2004 22:33:22 GMT  
 Using Win32OLE with Excel leaves Excel process hanging
Also
excel.ActiveWorkbook.Close(0);
probably wouldn't be a bad idea.

It seems like you were expecting a GC to clean up, in Ruby it can, but
there are problems interfacing with other progs.  Try
ObjectSpace#define_finalizer for a destructor.

Quote:

> I have created a neat little ruby program that generates a useful
> graph in Excel.  I keep the program on my desktop and doubleclick it
> whenever I want to see the graph.  Unfortunately, once I invoke this
> program, it seems to leave a process running in the system.  After
> 20min or so, it will stop working.  What's more, it often prevents
> other OLE operations from working in other applications.  It can be
> fixed by a logout/logon.  I'm using windows 2000.

> Any ideas?

> Here is the code for AmazonAnalyzer.rb

> require 'AmazonAnalyzer'
> require 'win32ole'

> if __FILE__ == $0
>   periodLength = 7
>   if (ARGV[0] != nil) && (ARGV[0].to_i > 0)
>     periodLength = ARGV[0].to_i
>   end

>   puts "Running Amazon Analyzer with period length #{periodLength}"

>   analyzer = AmazonAnalyzer.new()
>   analyzer.analyzeFile("bookData", periodLength)

>   excel = WIN32OLE.new("excel.application")
>   workbook = excel.Workbooks.Add()

>   col = 'b'
>   row = "1"
>   analyzer.startDate.step(analyzer.endDate,periodLength) {|date|
>     excel.Range(col+row).value = date.to_s
>     col = col.succ
>   }

>   isbns = analyzer.isbns
>   isbns.each {|isbn|
>     col='a'
>     row = row.succ
>     title = analyzer.getTitleFor(isbn)
>     title = title.split(':')[0]
>     title = title.split('(')[0]
>     title = title[0,40]
>     excel.Range(col+row).value = title

>     periods = analyzer.getPeriodsForIsbn(isbn)
>     0.upto(periods.max) {|period|
>       col = col.succ
>       if periods.include?(period)
>         excel.Range(col+row).value =
>           analyzer.getRange(isbn,period).mean
>       end
>     }
>   }
>   xlLine=4
>   xlValue=2
>   xlRows=1
>   xlScaleLogarithmic = -4133
>   excel.Range("a1:"+col+row).Select()
>   excelChart = workbook.Charts.Add()
>   excelChart.type = xlLine
>   excelChart.plotBy=xlRows
>   excelChart.Legend.Font.Size=8
>   excelChart.Axes(xlValue).scaleType = xlScaleLogarithmic
>   excelChart.Axes(xlValue).minimumScale = 100

>   workbook.saved = true
>   excel.visible = true
> end

> Robert C. Martin  | "Uncle Bob"

> PO Box 5757       | Tel: (800) 338-6716
> 565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
> Suite 135         |                               | www.XProgramming.com
> Vernon Hills, IL, | Training and Mentoring        | www.junit.org
> 60061             | OO, XP, Java, C++, Python     |

> "One of the great commandments of science is:
>     'Mistrust arguments from authority.'" -- Carl Sagan

--
Alexander Schofield


Wed, 11 Aug 2004 22:39:25 GMT  
 Using Win32OLE with Excel leaves Excel process hanging
On Sat, 23 Feb 2002 09:39:25 -0500, Alexander Schofield

Sorry, I should have been clearer.  The goal is for the Ruby program
to quit, leaving the Excel window on the screen.  The user can then
look at the Excel window for as long as they like and then close it.
By then the Ruby program is long gone.

So what I need is a way for the Ruby program to detach from the excel.
program, and leave it running.

Quote:
>Also
>excel.ActiveWorkbook.Close(0);
>probably wouldn't be a bad idea.

>It seems like you were expecting a GC to clean up, in Ruby it can, but
>there are problems interfacing with other progs.  Try
>ObjectSpace#define_finalizer for a destructor.


>> I have created a neat little ruby program that generates a useful
>> graph in Excel.  I keep the program on my desktop and doubleclick it
>> whenever I want to see the graph.  Unfortunately, once I invoke this
>> program, it seems to leave a process running in the system.  After
>> 20min or so, it will stop working.  What's more, it often prevents
>> other OLE operations from working in other applications.  It can be
>> fixed by a logout/logon.  I'm using windows 2000.

>> Any ideas?

>> Here is the code for AmazonAnalyzer.rb

>> require 'AmazonAnalyzer'
>> require 'win32ole'

>> if __FILE__ == $0
>>   periodLength = 7
>>   if (ARGV[0] != nil) && (ARGV[0].to_i > 0)
>>     periodLength = ARGV[0].to_i
>>   end

>>   puts "Running Amazon Analyzer with period length #{periodLength}"

>>   analyzer = AmazonAnalyzer.new()
>>   analyzer.analyzeFile("bookData", periodLength)

>>   excel = WIN32OLE.new("excel.application")
>>   workbook = excel.Workbooks.Add()

>>   col = 'b'
>>   row = "1"
>>   analyzer.startDate.step(analyzer.endDate,periodLength) {|date|
>>     excel.Range(col+row).value = date.to_s
>>     col = col.succ
>>   }

>>   isbns = analyzer.isbns
>>   isbns.each {|isbn|
>>     col='a'
>>     row = row.succ
>>     title = analyzer.getTitleFor(isbn)
>>     title = title.split(':')[0]
>>     title = title.split('(')[0]
>>     title = title[0,40]
>>     excel.Range(col+row).value = title

>>     periods = analyzer.getPeriodsForIsbn(isbn)
>>     0.upto(periods.max) {|period|
>>       col = col.succ
>>       if periods.include?(period)
>>         excel.Range(col+row).value =
>>           analyzer.getRange(isbn,period).mean
>>       end
>>     }
>>   }
>>   xlLine=4
>>   xlValue=2
>>   xlRows=1
>>   xlScaleLogarithmic = -4133
>>   excel.Range("a1:"+col+row).Select()
>>   excelChart = workbook.Charts.Add()
>>   excelChart.type = xlLine
>>   excelChart.plotBy=xlRows
>>   excelChart.Legend.Font.Size=8
>>   excelChart.Axes(xlValue).scaleType = xlScaleLogarithmic
>>   excelChart.Axes(xlValue).minimumScale = 100

>>   workbook.saved = true
>>   excel.visible = true
>> end

>> Robert C. Martin  | "Uncle Bob"

>> PO Box 5757       | Tel: (800) 338-6716
>> 565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
>> Suite 135         |                               | www.XProgramming.com
>> Vernon Hills, IL, | Training and Mentoring        | www.junit.org
>> 60061             | OO, XP, Java, C++, Python     |

>> "One of the great commandments of science is:
>>     'Mistrust arguments from authority.'" -- Carl Sagan

Robert C. Martin  | "Uncle Bob"                  

PO Box 5757       | Tel: (800) 338-6716        
565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
Suite 135         |                               | www.XProgramming.com
Vernon Hills, IL, | Training and Mentoring        | www.junit.org
60061             | OO, XP, Java, C++, Python     |

"One of the great commandments of science is:
    'Mistrust arguments from authority.'" -- Carl Sagan



Sat, 14 Aug 2004 22:57:41 GMT  
 Using Win32OLE with Excel leaves Excel process hanging
Hi Robert,

Quote:
>it seems to leave a process running in the system

A "ruby.exe" process?
What Ruby version are you running?
My "old" 1.6.3 has no problems alike.
Neither with excel nor with word.
Clemens

Quote:
> -----Original Message-----
> From: Robert C. Martin

> Sent: Dienstag, 26. Februar 2002 16:06

> Subject: Re: Using Win32OLE with Excel leaves Excel process{*filter*}

> On Sat, 23 Feb 2002 09:39:25 -0500, Alexander Schofield

> Sorry, I should have been clearer.  The goal is for the Ruby program
> to quit, leaving the Excel window on the screen.  The user can then
> look at the Excel window for as long as they like and then close it.
> By then the Ruby program is long gone.

> So what I need is a way for the Ruby program to detach from the excel.
> program, and leave it running.

> >Also
> >excel.ActiveWorkbook.Close(0);
> >probably wouldn't be a bad idea.

> >It seems like you were expecting a GC to clean up, in Ruby
> it can, but
> >there are problems interfacing with other progs.  Try
> >ObjectSpace#define_finalizer for a destructor.


> >> I have created a neat little ruby program that generates a useful
> >> graph in Excel.  I keep the program on my desktop and
> doubleclick it
> >> whenever I want to see the graph.  Unfortunately, once I
> invoke this
> >> program, it seems to leave a process running in the system.  After
> >> 20min or so, it will stop working.  What's more, it often prevents
> >> other OLE operations from working in other applications.  It can be
> >> fixed by a logout/logon.  I'm using windows 2000.

> >> Any ideas?

> >> Here is the code for AmazonAnalyzer.rb

> >> require 'AmazonAnalyzer'
> >> require 'win32ole'

> >> if __FILE__ == $0
> >>   periodLength = 7
> >>   if (ARGV[0] != nil) && (ARGV[0].to_i > 0)
> >>     periodLength = ARGV[0].to_i
> >>   end

> >>   puts "Running Amazon Analyzer with period length #{periodLength}"

> >>   analyzer = AmazonAnalyzer.new()
> >>   analyzer.analyzeFile("bookData", periodLength)

> >>   excel = WIN32OLE.new("excel.application")
> >>   workbook = excel.Workbooks.Add()

> >>   col = 'b'
> >>   row = "1"
> >>   analyzer.startDate.step(analyzer.endDate,periodLength) {|date|
> >>     excel.Range(col+row).value = date.to_s
> >>     col = col.succ
> >>   }

> >>   isbns = analyzer.isbns
> >>   isbns.each {|isbn|
> >>     col='a'
> >>     row = row.succ
> >>     title = analyzer.getTitleFor(isbn)
> >>     title = title.split(':')[0]
> >>     title = title.split('(')[0]
> >>     title = title[0,40]
> >>     excel.Range(col+row).value = title

> >>     periods = analyzer.getPeriodsForIsbn(isbn)
> >>     0.upto(periods.max) {|period|
> >>       col = col.succ
> >>       if periods.include?(period)
> >>         excel.Range(col+row).value =
> >>           analyzer.getRange(isbn,period).mean
> >>       end
> >>     }
> >>   }
> >>   xlLine=4
> >>   xlValue=2
> >>   xlRows=1
> >>   xlScaleLogarithmic = -4133
> >>   excel.Range("a1:"+col+row).Select()
> >>   excelChart = workbook.Charts.Add()
> >>   excelChart.type = xlLine
> >>   excelChart.plotBy=xlRows
> >>   excelChart.Legend.Font.Size=8
> >>   excelChart.Axes(xlValue).scaleType = xlScaleLogarithmic
> >>   excelChart.Axes(xlValue).minimumScale = 100

> >>   workbook.saved = true
> >>   excel.visible = true
> >> end

> >> Robert C. Martin  | "Uncle Bob"

> >> PO Box 5757       | Tel: (800) 338-6716
> >> 565 Lakeview Pkwy | Fax: (847) 573-1658           |

www.objectmentor.com

Quote:
>> Suite 135         |                               | www.XProgramming.com
>> Vernon Hills, IL, | Training and Mentoring        | www.junit.org
>> 60061             | OO, XP, Java, C++, Python     |

>> "One of the great commandments of science is:
>>     'Mistrust arguments from authority.'" -- Carl Sagan

Robert C. Martin  | "Uncle Bob"                  

PO Box 5757       | Tel: (800) 338-6716        
565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
Suite 135         |                               | www.XProgramming.com
Vernon Hills, IL, | Training and Mentoring        | www.junit.org
60061             | OO, XP, Java, C++, Python     |

"One of the great commandments of science is:
    'Mistrust arguments from authority.'" -- Carl Sagan



Sat, 14 Aug 2004 23:34:26 GMT  
 Using Win32OLE with Excel leaves Excel process hanging


Quote:
>Hi Robert,
>>it seems to leave a process running in the system

>A "ruby.exe" process?

No, an Excel process, or some Excel resource.

Quote:
>What Ruby version are you running?

1.6

Quote:
>My "old" 1.6.3 has no problems alike.
>Neither with excel nor with word.

Can you start up an Excel program, then exit the ruby program leaving
Excel running?  That's what seems to hang me up.

Quote:

>> -----Original Message-----
>> From: Robert C. Martin

>> Sent: Dienstag, 26. Februar 2002 16:06

>> Subject: Re: Using Win32OLE with Excel leaves Excel process{*filter*}

>> On Sat, 23 Feb 2002 09:39:25 -0500, Alexander Schofield

>> Sorry, I should have been clearer.  The goal is for the Ruby program
>> to quit, leaving the Excel window on the screen.  The user can then
>> look at the Excel window for as long as they like and then close it.
>> By then the Ruby program is long gone.

>> So what I need is a way for the Ruby program to detach from the excel.
>> program, and leave it running.

>> >Also
>> >excel.ActiveWorkbook.Close(0);
>> >probably wouldn't be a bad idea.

>> >It seems like you were expecting a GC to clean up, in Ruby
>> it can, but
>> >there are problems interfacing with other progs.  Try
>> >ObjectSpace#define_finalizer for a destructor.


>> >> I have created a neat little ruby program that generates a useful
>> >> graph in Excel.  I keep the program on my desktop and
>> doubleclick it
>> >> whenever I want to see the graph.  Unfortunately, once I
>> invoke this
>> >> program, it seems to leave a process running in the system.  After
>> >> 20min or so, it will stop working.  What's more, it often prevents
>> >> other OLE operations from working in other applications.  It can be
>> >> fixed by a logout/logon.  I'm using windows 2000.

>> >> Any ideas?

>> >> Here is the code for AmazonAnalyzer.rb

>> >> require 'AmazonAnalyzer'
>> >> require 'win32ole'

>> >> if __FILE__ == $0
>> >>   periodLength = 7
>> >>   if (ARGV[0] != nil) && (ARGV[0].to_i > 0)
>> >>     periodLength = ARGV[0].to_i
>> >>   end

>> >>   puts "Running Amazon Analyzer with period length #{periodLength}"

>> >>   analyzer = AmazonAnalyzer.new()
>> >>   analyzer.analyzeFile("bookData", periodLength)

>> >>   excel = WIN32OLE.new("excel.application")
>> >>   workbook = excel.Workbooks.Add()

>> >>   col = 'b'
>> >>   row = "1"
>> >>   analyzer.startDate.step(analyzer.endDate,periodLength) {|date|
>> >>     excel.Range(col+row).value = date.to_s
>> >>     col = col.succ
>> >>   }

>> >>   isbns = analyzer.isbns
>> >>   isbns.each {|isbn|
>> >>     col='a'
>> >>     row = row.succ
>> >>     title = analyzer.getTitleFor(isbn)
>> >>     title = title.split(':')[0]
>> >>     title = title.split('(')[0]
>> >>     title = title[0,40]
>> >>     excel.Range(col+row).value = title

>> >>     periods = analyzer.getPeriodsForIsbn(isbn)
>> >>     0.upto(periods.max) {|period|
>> >>       col = col.succ
>> >>       if periods.include?(period)
>> >>         excel.Range(col+row).value =
>> >>           analyzer.getRange(isbn,period).mean
>> >>       end
>> >>     }
>> >>   }
>> >>   xlLine=4
>> >>   xlValue=2
>> >>   xlRows=1
>> >>   xlScaleLogarithmic = -4133
>> >>   excel.Range("a1:"+col+row).Select()
>> >>   excelChart = workbook.Charts.Add()
>> >>   excelChart.type = xlLine
>> >>   excelChart.plotBy=xlRows
>> >>   excelChart.Legend.Font.Size=8
>> >>   excelChart.Axes(xlValue).scaleType = xlScaleLogarithmic
>> >>   excelChart.Axes(xlValue).minimumScale = 100

>> >>   workbook.saved = true
>> >>   excel.visible = true
>> >> end

>> >> Robert C. Martin  | "Uncle Bob"

>> >> PO Box 5757       | Tel: (800) 338-6716
>> >> 565 Lakeview Pkwy | Fax: (847) 573-1658           |
>www.objectmentor.com
>>> Suite 135         |                               | www.XProgramming.com
>>> Vernon Hills, IL, | Training and Mentoring        | www.junit.org
>>> 60061             | OO, XP, Java, C++, Python     |

>>> "One of the great commandments of science is:
>>>     'Mistrust arguments from authority.'" -- Carl Sagan

>Robert C. Martin  | "Uncle Bob"                  

>PO Box 5757       | Tel: (800) 338-6716        
>565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
>Suite 135         |                               | www.XProgramming.com
>Vernon Hills, IL, | Training and Mentoring        | www.junit.org
>60061             | OO, XP, Java, C++, Python     |

>"One of the great commandments of science is:
>    'Mistrust arguments from authority.'" -- Carl Sagan

Robert C. Martin  | "Uncle Bob"                  

PO Box 5757       | Tel: (800) 338-6716        
565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
Suite 135         |                               | www.XProgramming.com
Vernon Hills, IL, | Training and Mentoring        | www.junit.org
60061             | OO, XP, Java, C++, Python     |

"One of the great commandments of science is:
    'Mistrust arguments from authority.'" -- Carl Sagan



Mon, 16 Aug 2004 22:42:50 GMT  
 Using Win32OLE with Excel leaves Excel process hanging

Quote:
> -----Original Message-----
> From: Robert C. Martin

> Sent: Thursday, February 28, 2002 10:08 AM

> Subject: Re: Using Win32OLE with Excel leaves Excel process{*filter*}
[...]

> No, an Excel process, or some Excel resource.

> >What Ruby version are you running?

> 1.6

> >My "old" 1.6.3 has no problems alike.
> >Neither with excel nor with word.

> Can you start up an Excel program, then exit the ruby program leaving
> Excel running?  That's what seems to hang me up.

I believe you should call a .Quit method on your Excel object...

Christophe



Mon, 16 Aug 2004 23:48:39 GMT  
 Using Win32OLE with Excel leaves Excel process hanging
In message "Re: Using Win32OLE with Excel leaves Excel process{*filter*}"

Quote:
> >A "ruby.exe" process?

> No, an Excel process, or some Excel resource.

> Can you start up an Excel program, then exit the ruby program leaving
> Excel running?  That's what seems to hang me up.

What the following script output on your environment?

  require 'win32ole'
  puts "Win32OLE: #{WIN32OLE::VERSION}"
  puts "Ruby: #{RUBY_VERSION}(#{RUBY_RELEASE_DATE})"

And what version of Excel do you use?

I'm not sure, but I am afraid this is Win32OLE bug.
So, I want to try your script to fix the bug if this is Win32OLE bug,
but I can't find AmazonAnalyzer.rb. How can I get AmazonAnalyzer.rb?
Or, could you show me the script without AmazonAnalyzer.rb which
hang you up?

  Regards,
  Masaki Suketa



Wed, 18 Aug 2004 15:33:39 GMT  
 Using Win32OLE with Excel leaves Excel process hanging
On Sat, 02 Mar 2002 07:33:39 GMT, Masaki Suketa

Quote:

>In message "Re: Using Win32OLE with Excel leaves Excel process{*filter*}"

>> >A "ruby.exe" process?

>> No, an Excel process, or some Excel resource.

>> Can you start up an Excel program, then exit the ruby program leaving
>> Excel running?  That's what seems to hang me up.

>What the following script output on your environment?

>  require 'win32ole'
>  puts "Win32OLE: #{WIN32OLE::VERSION}"
>  puts "Ruby: #{RUBY_VERSION}(#{RUBY_RELEASE_DATE})"

irb(main):002:0*   require 'win32ole'
true
irb(main):003:0>   puts "Win32OLE: #{WIN32OLE::VERSION}"
Win32OLE: 0.2.6
nil
irb(main):004:0>   puts "Ruby: #{RUBY_VERSION}(#{RUBY_RELEASE_DATE})"
Ruby: 1.6.4(2001-06-04)

Quote:

>And what version of Excel do you use?

Excel 2000 9.0.2720

Quote:

>I'm not sure, but I am afraid this is Win32OLE bug.
>So, I want to try your script to fix the bug if this is Win32OLE bug,
>but I can't find AmazonAnalyzer.rb. How can I get AmazonAnalyzer.rb?
>Or, could you show me the script without AmazonAnalyzer.rb which
>hang you up?

AmazonAnalyzer.rb----
require 'ranking'
require 'date'
require 'pstore'

class DataRange
  def initialize(rangeArray)

  end
  def to_s
    "(%d:%d:%d)[%d]"%[min,mean,max,n]
  end

  def min

  end

  def max

  end

  def mean
    sum = 0


  end

  def n

  end
end

class AmazonAnalyzer
  attr :titleTable
  attr :rangeTable
  attr :startDate
  attr :endDate

  attr_writer :periodLength

  def analyzeFile(bookDataFile, periodLength)
    bookData = {}
    store = PStore.new(bookDataFile)
    store.transaction do
      store.roots.each {|dateString|
      bookData[dateString] = store[dateString]
    }
    end
    analyzeBookData(bookData, periodLength)
  end

  def analyzeBookData(bookData, periodLength)



  end

  def isbns

  end

  def getTitleFor(isbn)

  end

  def getPeriodsForIsbn(isbn)
    rangeTable[isbn].keys
  end

  def getRange(isbn, period)
    rangeTable[isbn][period]
  end

  def makeTitleTable(dateTable)
    titleTable = {}
    dateTable.each_value {|rankingList|
      rankingList.each {|ranking|
    titleTable[ranking.isbn] = ranking.title
      }
    }
    return titleTable
  end

  def makeIsbnDateRankingTable(dateTable)
    isbnDateRankingTable = {}
    dateTable.each {
      |date, rankingList|
      rankingList.each {|ranking|
    if (isbnDateRankingTable[ranking.isbn] == nil)
      isbnDateRankingTable[ranking.isbn] = []
    end
    isbnDateRankingTable[ranking.isbn] << [date, ranking.rank]
      }
    }
    return isbnDateRankingTable
  end

  def makeIsbnPeriodTable(isbnDateRankingTable)
    isbnPeriodTable = {}
    isbnDateRankingTable.each {
      |isbn, dateRankingList|
      dateRankingList.each {|dateRanking|
    date = dateRanking[0]
    ranking = dateRanking[1]
    period = getPeriod(date)
    if isbnPeriodTable[isbn] == nil
      isbnPeriodTable[isbn] = {}
    end
    if isbnPeriodTable[isbn][period] == nil
      isbnPeriodTable[isbn][period] = []
    end
    isbnPeriodTable[isbn][period] << ranking
      }
    }
    return isbnPeriodTable
  end




  def generatePeriods(dateStrings)
    dates = []
    dateStrings.each {
      |dateString|
      dates << makeDate(dateString)
    }


  end

  def makeDate(dateString)
    dateString =~ /(\d\d\d\d)-(\d\d)-(\d\d)/
    year = $1.to_i
    month = $2.to_i
    day = $3.to_i
    return Date.new(year, month, day)
  end

  def getPeriod(dateString)
    date = makeDate(dateString)


    return period
  end

  def analyze(bookData)
    generatePeriods(bookData.keys)
    isbnDateRankingTable = makeIsbnDateRankingTable(bookData)
    isbnPeriodHash = makeIsbnPeriodTable(isbnDateRankingTable)
    return RangeTable.new(isbnPeriodHash)
  end

  def getPeriodStartDate(period)

  end
end

class RangeTable # [isbn][period] => DataRange
  def initialize(isbnPeriodHash)



  end

  def periods(isbn)
    getPeriods(isbn).keys
  end

  def [] (isbn)
    getPeriods(isbn)
  end

  def getPeriods(isbn)

    periodHash = {}

      periods.each {|period|

    periodHash[period] = createDataRange(ranks)
      }

    return periodHash
  end

  def createDataRange(rangeArray)
    return DataRange.new(rangeArray)
  end
end

if __FILE__ == $0
  periodLength = 1
  if (ARGV[0] != nil) && (ARGV[0].to_i > 0)
    periodLength = ARGV[0].to_i
  end

  analyzer = AmazonAnalyzer.new()
  analyzer.analyzeFile("bookData", periodLength)

  isbns = analyzer.isbns
  isbns.each {|isbn|
    print "\n----- ",analyzer.getTitleFor(isbn)," -----------\n"
    periods = analyzer.getPeriodsForIsbn(isbn)
    periods.sort!
    periods.each {|period|
      print "   Period:",analyzer.getPeriodStartDate(period),
    " - ",analyzer.getRange(isbn,period),"\n"
    }
  }
end

Ranking.rb------

class Ranking
  attr_reader :isbn, :title, :rank
  def initialize(isbn, title, rank)



  end

  def print

  end

  def <=>(ranking)

  end
end

-----

You'll need the data file too.  I'll email it to you.

Robert C. Martin  | "Uncle Bob"                  

PO Box 5757       | Tel: (800) 338-6716        
565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
Suite 135         |                               | www.XProgramming.com
Vernon Hills, IL, | Training and Mentoring        | www.junit.org
60061             | OO, XP, Java, C++, Python     |

"One of the great commandments of science is:
    'Mistrust arguments from authority.'" -- Carl Sagan



Wed, 18 Aug 2004 21:17:51 GMT  
 Using Win32OLE with Excel leaves Excel process hanging
On Thu, 28 Feb 2002 15:48:39 GMT, Christophe Broult

Quote:

>> -----Original Message-----
>> From: Robert C. Martin

>> Sent: Thursday, February 28, 2002 10:08 AM

>> Subject: Re: Using Win32OLE with Excel leaves Excel process{*filter*}
>[...]

>> No, an Excel process, or some Excel resource.

>> >What Ruby version are you running?

>> 1.6

>> >My "old" 1.6.3 has no problems alike.
>> >Neither with excel nor with word.

>> Can you start up an Excel program, then exit the ruby program leaving
>> Excel running?  That's what seems to hang me up.

>I believe you should call a .Quit method on your Excel object...

Except that I want to leave it running so that the user can look at
the graph.  I want the *user* to quit excel.

Robert C. Martin  | "Uncle Bob"                  

PO Box 5757       | Tel: (800) 338-6716        
565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
Suite 135         |                               | www.XProgramming.com
Vernon Hills, IL, | Training and Mentoring        | www.junit.org
60061             | OO, XP, Java, C++, Python     |

"One of the great commandments of science is:
    'Mistrust arguments from authority.'" -- Carl Sagan



Wed, 18 Aug 2004 21:22:19 GMT  
 Using Win32OLE with Excel leaves Excel process hanging
Hello, Robert, Thank you for your report!
In message "Re: Using Win32OLE with Excel leaves Excel process{*filter*}"

Quote:
> irb(main):002:0*   require 'win32ole'
> true
> irb(main):003:0>   puts "Win32OLE: #{WIN32OLE::VERSION}"
> Win32OLE: 0.2.6
> nil
> irb(main):004:0>   puts "Ruby: #{RUBY_VERSION}(#{RUBY_RELEASE_DATE})"
> Ruby: 1.6.4(2001-06-04)

Win32OLE 0.2.6 is too old. Try the latest version 0.4.0.
My environment is Windows 2000, Excel 2000, Win32OLE 0.4.0
and your script works fine on my environment.
(I can stop excel.exe process after ruby.exe process terminated.)

  Regards,
  Masaki Suketa



Thu, 19 Aug 2004 22:26:48 GMT  
 Using Win32OLE with Excel leaves Excel process hanging
I understand what you mean now, but I'm afraid I can't help.  Mainly
because I can't seem to recreate the problem on my machine (with office
97).  Excel is left standing, and Ruby exits fine (when i use rubyw, irb
kills it).  At the risk of being pelted by stones may I suggest that you
prototype this in some M$ technology?  Vba or even WSH possibly.  It's
just a lot easier when you have an object/method browser to play with.
Otherwise I found #ole_methods useful from irb in experimenting on the
fly.

Quote:

> On Sat, 23 Feb 2002 09:39:25 -0500, Alexander Schofield

> Sorry, I should have been clearer.  The goal is for the Ruby program
> to quit, leaving the Excel window on the screen.  The user can then
> look at the Excel window for as long as they like and then close it.
> By then the Ruby program is long gone.

> So what I need is a way for the Ruby program to detach from the excel.
> program, and leave it running.

> >Also
> >excel.ActiveWorkbook.Close(0);
> >probably wouldn't be a bad idea.

> >It seems like you were expecting a GC to clean up, in Ruby it can, but
> >there are problems interfacing with other progs.  Try
> >ObjectSpace#define_finalizer for a destructor.


> >> I have created a neat little ruby program that generates a useful
> >> graph in Excel.  I keep the program on my desktop and doubleclick it
> >> whenever I want to see the graph.  Unfortunately, once I invoke this
> >> program, it seems to leave a process running in the system.  After
> >> 20min or so, it will stop working.  What's more, it often prevents
> >> other OLE operations from working in other applications.  It can be
> >> fixed by a logout/logon.  I'm using windows 2000.

> >> Any ideas?

> >> Here is the code for AmazonAnalyzer.rb

> >> require 'AmazonAnalyzer'
> >> require 'win32ole'

> >> if __FILE__ == $0
> >>   periodLength = 7
> >>   if (ARGV[0] != nil) && (ARGV[0].to_i > 0)
> >>     periodLength = ARGV[0].to_i
> >>   end

> >>   puts "Running Amazon Analyzer with period length #{periodLength}"

> >>   analyzer = AmazonAnalyzer.new()
> >>   analyzer.analyzeFile("bookData", periodLength)

> >>   excel = WIN32OLE.new("excel.application")
> >>   workbook = excel.Workbooks.Add()

> >>   col = 'b'
> >>   row = "1"
> >>   analyzer.startDate.step(analyzer.endDate,periodLength) {|date|
> >>     excel.Range(col+row).value = date.to_s
> >>     col = col.succ
> >>   }

> >>   isbns = analyzer.isbns
> >>   isbns.each {|isbn|
> >>     col='a'
> >>     row = row.succ
> >>     title = analyzer.getTitleFor(isbn)
> >>     title = title.split(':')[0]
> >>     title = title.split('(')[0]
> >>     title = title[0,40]
> >>     excel.Range(col+row).value = title

> >>     periods = analyzer.getPeriodsForIsbn(isbn)
> >>     0.upto(periods.max) {|period|
> >>       col = col.succ
> >>       if periods.include?(period)
> >>         excel.Range(col+row).value =
> >>           analyzer.getRange(isbn,period).mean
> >>       end
> >>     }
> >>   }
> >>   xlLine=4
> >>   xlValue=2
> >>   xlRows=1
> >>   xlScaleLogarithmic = -4133
> >>   excel.Range("a1:"+col+row).Select()
> >>   excelChart = workbook.Charts.Add()
> >>   excelChart.type = xlLine
> >>   excelChart.plotBy=xlRows
> >>   excelChart.Legend.Font.Size=8
> >>   excelChart.Axes(xlValue).scaleType = xlScaleLogarithmic
> >>   excelChart.Axes(xlValue).minimumScale = 100

> >>   workbook.saved = true
> >>   excel.visible = true
> >> end

> >> Robert C. Martin  | "Uncle Bob"

> >> PO Box 5757       | Tel: (800) 338-6716
> >> 565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
> >> Suite 135         |                               | www.XProgramming.com
> >> Vernon Hills, IL, | Training and Mentoring        | www.junit.org
> >> 60061             | OO, XP, Java, C++, Python     |

> >> "One of the great commandments of science is:
> >>     'Mistrust arguments from authority.'" -- Carl Sagan

> Robert C. Martin  | "Uncle Bob"

> PO Box 5757       | Tel: (800) 338-6716
> 565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
> Suite 135         |                               | www.XProgramming.com
> Vernon Hills, IL, | Training and Mentoring        | www.junit.org
> 60061             | OO, XP, Java, C++, Python     |

> "One of the great commandments of science is:
>     'Mistrust arguments from authority.'" -- Carl Sagan

--
Alexander Schofield


Fri, 20 Aug 2004 10:41:54 GMT  
 Using Win32OLE with Excel leaves Excel process hanging
On Sun, 03 Mar 2002 14:26:48 GMT, Masaki Suketa

Quote:

>Hello, Robert, Thank you for your report!
>In message "Re: Using Win32OLE with Excel leaves Excel process{*filter*}"

>> irb(main):002:0*   require 'win32ole'
>> true
>> irb(main):003:0>   puts "Win32OLE: #{WIN32OLE::VERSION}"
>> Win32OLE: 0.2.6
>> nil
>> irb(main):004:0>   puts "Ruby: #{RUBY_VERSION}(#{RUBY_RELEASE_DATE})"
>> Ruby: 1.6.4(2001-06-04)

>Win32OLE 0.2.6 is too old. Try the latest version 0.4.0.
>My environment is Windows 2000, Excel 2000, Win32OLE 0.4.0
>and your script works fine on my environment.
>(I can stop excel.exe process after ruby.exe process terminated.)

Thank you!  I should have thought of that myself before posting here.

Robert C. Martin  | "Uncle Bob"                  

PO Box 5757       | Tel: (800) 338-6716        
565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
Suite 135         |                               | www.XProgramming.com
Vernon Hills, IL, | Training and Mentoring        | www.junit.org
60061             | OO, XP, Java, C++, Python     |

You and I can enjoy the experience of not always seeing
eye-to-eye, but we can also respect each other, even when
you might find some idea of mine totally ludicrous.
    -- Richard Riehle



Fri, 20 Aug 2004 22:44:16 GMT  
 
 [ 13 post ] 

 Relevant Pages 

1. how to create an excel workbook using the excel toolkit

2. win32ole and excel

3. realbasic to excel/realbasic vers excel

4. Excel toolkit --excel save.vi is broken arrow

5. after opening an excel doc with actve X, crashes when calling excel again

6. Calling DVF dll from Excel: Excel macro problem?

7. Looking for Excel API library (XLCALL.LIB) for Excel 7

8. convert txt excel file without open the excel application

9. Inter process communication using Tk/send hangs on suspended processes

10. Using OLE to access Excel

11. Using formula in SoftMasters Office Excel MSExcelAssign Method

12. Using Excel with DDE

 

 
Powered by phpBB® Forum Software