From: Tom Koenig <tomkoenig@...>
Date: 2008-03-17T03:16:00+09:00
Subject: Re: Sorting Excel table with OLE

Damjan Rems wrote:
> 
> Can anybody tell, how to sort Excel table with OLE automation?
Here's a somewhat verbose answer, that builds on the previous responses.
As always when trying to automate a script in Ruby, it helps to record 
the macro.  Then if you're using WIN32OLE, VBScript can usually be 
translated directly into Ruby. The tricky thing is usually figuring out 
what object to invoke a method against.

However, a naïve translation of the following, will not work:
Range("A1:C5").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range( _
"B2"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1 MatchCase:= _
False,Orientation:=xlTopToBottom,DataOption1:=xlSortNormal,DataOption2 _
:=xlSortNormal

Specifically, this translation fails:
require 'win32ole'
excel = WIN32OLE.new("excel.application")
wb =excel.Workbooks.Open("C:\\Spreadsheet.xls")
ws = spreadsheet.Worksheets(1)
ws.Range("A1:C5").Select
ws.Selection.Sort(Key1:=ws.Range("A1"), Order1:=xlAscending, 
Key2:=ws.Range("C1"), Order2:=xlAscending,  Header:=xlYes, 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom)

There are at least three reasons this doesn’t work.
1.  It turns out that "Selection" is a property of the Application, not 
of the Sheet. We could fix this by substituting
sr = Range("A1:C5").Select
sr.Sort ..
But generally we want replace selection with direct references to a 
range.  In the case of sort, the usedRange method comes in handy. So we 
start with something like this.
ws.usedRange.Sort...
2.  VBA uses named arguments, but Ruby uses positional arguments only. 
However, use can use associations to achieve very much the same thing as 
named arguements.  So
Key1:=ws.Range("A1"),
becomes
‘Key1’=>ws.Range("A1"),
That is, you put the keyword in quotes, replace the VB assignment 
operator with the Ruby association operator, the value is entered as it 
was, and each association is separated by a comma.
3.  Finally, xlAscending, xlNo, xlTopToBottom are constants, which Ruby 
doesn't know to translate. You may want to substitute actual numbers for 
these constants .The following snippet will give you a complete list of 
the translations:
class Excel_Const
end
require 'win32ole'
excel = WIN32OLE.new('Excel.Application')
WIN32OLE.const_load(excel, Excel_Const)
Excel_Const.constants.sort.each {|const|
    value = eval("Excel_Const::#{const}")
    puts ' '*4 + const + ' => ' + value.to_s
}


So a reasonable translation becomes
require 'win32ole'
excel = WIN32OLE.new("excel.application")
wb =excel.Workbooks.Open("C:\\Spreadsheet.xls")
ws = wb.Worksheets(1)
ws.usedRange.Sort('key1'=>ws.Range("A1") , 'Order1'=>1, 
'Key2'=>ws.Range("C1"), 'Order1'=>1, 'header'=>1, 'OrderCustom'=>1, 
'MatchCase'=>false, 'Orientation'=>1)
If you prefer to not use magic numbers, you can create a class something 
like  Excel_Extension.rb
class Excel_Extension
  require 'win32ole'
  attr_reader :excel
  class ExcelConst
  end

  def initialize
    @excel = WIN32OLE.new('Excel.Application')
   WIN32OLE.const_load(excel, ExcelConst)
  end

  def xl(constant)
    begin
       excel_constant=constant.sub(/xl/, 'Xl') # allow constant to be xl 
or Xl
       return eval("ExcelConst::#{excel_constant}")
    rescue
      puts(excel_constant.to_s + " not recognized as an Excel constant")
      return 1
   end
  end
end

And use it like so:
load ' Excel_Extension.rb'
ee= Excel_Extension.new
wb =ee.excel.Workbooks.Open(“C:\\Spreadsheet.xls")
ws = wb.Worksheets(1)
ws.usedRange.Sort('key1'=>ws.Range("A1") , 
'Order1'=>ee.xl('xlAscending'),
  'Key2'=>ws.Range("C1"), 'Order1'=>ee.xl('xlAscending'),
  'header'=>ee.xl('xlYes'), 'OrderCustom'=>1, 'MatchCase'=>false,
  'Orientation'=>ee.xl('xlTopToBottom'))
-- 
Posted via http://www.ruby-forum.com/.