From: "Robert C. Martin" <u.n.c.l.e.b.o.b.@...>
Date: 2002-02-23T23:21:14+09:00
Subject: 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"                   
Object Mentor Inc.| unclebob @ objectmentor . com
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