From: "Peña, Botp" <botp@...>
Date: 2007-03-29T16:17:47+09:00
Subject: Re: excel is not closed in runtime using excel.Quit()

On Behalf Of Ruhul Amin:
# This is my working code.I can not understand how 
# can I use the single Excel instance for different files.

just open an excel instance or app. 
   fr there, nest the opening and 
   closing of multiple workbooks. 
close/quit the excel app when finish.

to use ruby's power and elegance, you may create an excel class that automatically closes the opened instances and or workbooks.

eg.

C:\family\ruby\excel>cat test4.rb

#---------------------------------------------------------------
# make an Excel class to simplify opening & closing of classes
# place this in a separate file, and just require it
#---------------------------------------------------------------
class Excel
   attr_accessor :excel
   def initialize
      require 'win32ole'
      @excel = WIN32OLE::new('Excel.Application')
      yield self
      @excel.Quit
   end

   def open_book file
      book = self.excel.Workbooks.Open file
      yield book
      self.excel.ActiveWorkbook.Close(0)
   end
end

#---------------------------------------------------------------
# let's try using our excel class
#---------------------------------------------------------------
# declare array of files we want to read
Xfiles = %w(
            c:\\family\\ruby\\test.xls
            c:\\family\\ruby\\test1.xls
            c:\\family\\ruby\\test2.xls
            c:\\family\\ruby\\test3.xls
            c:\\family\\ruby\\test4.xls
            c:\\temp\\test1.xls
            c:\\temp\\test2.xls
            )

#----------------
# ok, lets do it!
#----------------

Excel.new do |excel|    # instanciate excel
   Xfiles.each_with_index do |file,i|  # let's loop on each file
      excel.open_book(file) do |workbook|   

         # do whatever you want to do with your workbook here
         worksheet = workbook.Worksheets(1) 
         worksheet.Select      # select the worksheet
         title = worksheet.Range('H3')['Value']  #get value of title

         # we just display the count, filename, and H3 value
         puts "#{i+1}:"
         puts "   pathname : #{file}"
         puts "   title:     #{title}"

      end #excel.open_book
   end #xfiles.each
end #Excel.open


C:\family\ruby\excel>ruby test4.rb
1:
   pathname : c:\family\ruby\test.xls
   title:     Title of Workbook of file test
2:
   pathname : c:\family\ruby\test1.xls
   title:     this is test 1 title
3:
   pathname : c:\family\ruby\test2.xls
   title:     the value of h3 :)
4:
   pathname : c:\family\ruby\test3.xls
   title:     another title for test3 file
5:
   pathname : c:\family\ruby\test4.xls
   title:     Title of Workbook
6:
   pathname : c:\temp\test1.xls
   title:     Title of Workbook
7:
   pathname : c:\temp\test2.xls
   title:     Title of Workbook

C:\family\ruby\excel>

in this example, it is important to note that
   1  we did not have to specify the closing of the instance/workbooks
   2  code blocks allowed us to very flexible
   3  using array#each we didn't need to count

the above run took less than 2 seconds to finish; and i didn't even notice any tiny memory hiccup whatsoever. for 100 average excel files, it should take you less than a minute to process all.

hth.
kind regards -botp