On Behalf Of Ruhul A.:
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