Sorting Excel table with OLE

Ruby
1

Can anybody tell, how to sort Excel table with OLE automation?

What I need is something like this:
excel.Sort(‘On ColumnB’).Range(“a2:e5000”)

Than you

TheR

2

On Mar 23, 3:13 am, Damjan R. [email protected] wrote:

Posted viahttp://www.ruby-forum.com/.
xlAscending = 1
xlDescending = 2
excel.Range(“a2:e5000”).Sort(excel.Range(“b2”), xlAscending)

Mully

3

David M. wrote:

On Mar 23, 3:13 am, Damjan R. [email protected] wrote:

Posted viahttp://www.ruby-forum.com/.
xlAscending = 1
xlDescending = 2
excel.Range(“a2:e5000”).Sort(excel.Range(“b2”), xlAscending)

Thank you very much.

I was looking for references on net (first 5 pages on google), but
couldn’t find anything solid. Do you know any good reference site
(beside your blog pages ofcourse).

by

TheR

4

From: [email protected] [mailto:[email protected]] On Behalf
Of
Damjan R.
Sent: Friday, March 23, 2007 3:34 PM

I was looking for references on net (first 5 pages on google), but
couldn’t find anything solid. Do you know any good reference site
(beside your blog pages ofcourse).

For this (and similar) cases, the best “reference” would be: writing
macro
in Excel, read its VBA code and translate it to Ruby (the translation is
pretty straightforward).

V.

5

Damjan R. 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’))