Sort Excel spreadsheet with Ruby?

ruby-talk
1

Has anyone successfully used Ruby to sort an Excel spreadsheet? It has been
said that VBScript usually translates directly to Ruby if you’re using
WIN32OLE. For the most part, I have found this to be true. However, this
example translated from VBScript fails on the Sort line:

#/usr/bin env ruby
require 'win32ole’
excel = WIN32OLE.new(“excel.application”)
excel[‘Visible’] = TRUE
spreadsheet =
excel.Workbooks.Open(“C:\Path\to\your\spreadsheet\Spreadsheet.xls”)
sheet = spreadsheet.Worksheets(1)
sheet.Cells.Select
sheet.Selection.Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlNo,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

How would one translate this to Ruby? If you comment out the Sort line,
Ruby will succeed in running to the Cells.Select point.
v/r-
Craig

2

Moran, Craig M (BAH) wrote:

Has anyone successfully used Ruby to sort an Excel spreadsheet? It has been
said that VBScript usually translates directly to Ruby if you’re using
WIN32OLE. For the most part, I have found this to be true. However, this
example translated from VBScript fails on the Sort line:
[…]
sheet.Selection.Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlNo,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Is Sort expecting a Hash argument?

In that case, you would probably be more successful using => instead of
:= for all the arguments in that line.

Never used WIN32OLE, so YMMV and HTH :slight_smile:

···

–
([ Kent Dahl ]/)_ ~ [ Kent Dahl - Kent Dahl ]/~
))_student_/(( _d L b_/ Master of Science in Technology )
( __õ|õ// ) ) Industrial economics and technology management (
_/ö____/ (_engineering.discipline=Computer::Technology)

3

— “Moran, Craig M (BAH)” MoranCM@navair.navy.mil
wrote:

Has anyone successfully used Ruby to sort an Excel
spreadsheet?

No … and for a reason I will soon come to :wink:

It has been said that VBScript usually translates
directly to Ruby if you’re using WIN32OLE.
For the most part, I have found this to
be true.

Yes, for the most part.

However, this
example translated from VBScript fails on the Sort
line:

#/usr/bin env ruby
require ‘win32ole’
excel = WIN32OLE.new(“excel.application”)
excel[‘Visible’] = TRUE
spreadsheet =

excel.Workbooks.Open(“C:\Path\to\your\spreadsheet\Spreadsheet.xls”)

sheet = spreadsheet.Worksheets(1)
sheet.Cells.Select
sheet.Selection.Sort Key1:=Range(“A1”),
Order1:=xlAscending, Header:=xlNo,
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

How would one translate this to Ruby?

Couple of things to be aware of, here:

  1. Selection is not a property of sheet, it is a
    property of Application. (You will get this info from
    the Object Browser in VB).

So your code should be

excel.Selection.Sort
  1. The Sort method takes many arguments and in Ruby
    there is no way (yet) to skip them or call by name
    since it is an “external” method (i.e. not written in
    Ruby :-)). You will have to provide the entire list
    with correct default values. This is a trip in itself
    !
    You will have to study the call interface from here:
    Technical documentation | Microsoft Learn

Remember to convert all the VBA constants to Ruby
Constants. There is a nice way to do this (which I
forgot) but searching ruby-talk with Excel Constants
should bring it up.

  1. And finally, and most importantly, the Sort method
    has to be “exposed” in Ruby. Try this

require ‘win32ole’
excel = WIN32OLE.new(“excel.application”)
p excel.ole_methods.include?(“Sort”) # will return
false

At this point, I would bow to the higher gods and pray
:slight_smile:

HTH,
– shanko

···

Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th
http://taxes.yahoo.com/filing.html

4

“Moran, Craig M (BAH)” MoranCM@navair.navy.mil wrote in message news:B9500E218FB4D2119DE30000F810BBBF0F117705@nems13.nawcad.navy.mil…

require ‘win32ole’
excel = WIN32OLE.new(“excel.application”)
excel[‘Visible’] = TRUE
spreadsheet =
excel.Workbooks.Open(“C:\Path\to\your\spreadsheet\Spreadsheet.xls”)
sheet = spreadsheet.Worksheets(1)
…

I was able to perform a simple sort by replacing your code:

sheet.Cells.Select
sheet.Selection.Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlNo,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

for this:
rng = sheet.Cells.Range(“A:B”) # or some other range
sheet.Cells.Sort(rng)

I haven’t tried all the optional arguments yet, but at least this
gives you a minimally working code snippet.

Cheers,

  • alan
5

There are a few problems with the code. For one, these values:
xlAscending, xlNo, xlTopToBottom
are constants, but Ruby doesn’t know the value of these constants
because they’re stored in the Excel library. Therefore, you need to
substitute actual numbers for these. Here are the numeric values of
those constants:
xlAscending = 1, xlNo = 2, xlTopToBottom = 1
The other problem is that VBA uses named arguments, but I don’t think
Ruby uses named arguments; that means you can’t say “Header:=xlNo”.
The third problem is that “Selection” is a property of the
Application, not of the Sheet, so even in VBA your code failed for me.
This line will work for your code:
excel.Selection.Sort(sheet.Range(“A1”))
However, that uses all the default values. I’m still trying to figure
out how to incorporate all the arguments of the Sort routine, but I
suspect that you need to pass blanks for the unneeded arguments, and
you definitely need to pass actual numbers in places of the Excel
constants. Here’s the Sort routine:
…Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header,
OrderCustom, MatchCase, Orientation, SortMethod,
IgnoreControlCharacters, IgnoreDiacritics,
IgnoreKashida)
The working code will probably look something like this:
excel.Selection.Sort(sheet.Range(“A1”), 1, ‘’,’’,’’,’’,’’,2, 1, False,
1)

6

“Moran, Craig M (BAH)” MoranCM@navair.navy.mil wrote in message

Has anyone successfully used Ruby to sort an Excel spreadsheet?

One ugly work around would be to define a Macro (say SortMacro) in the
spread-sheet which does the sort as required and then do:

···

#---------------------------------------------------------------------------

require ‘win32ole’
excel = WIN32OLE.new(“excel.application”)
excel[‘Visible’] = TRUE
spreadsheet =

excel.Workbooks.Open(“C:\Path\to\your\spreadsheet\Spreadsheet.xls”)
sheet = spreadsheet.Worksheets(1)
sheet.Cells.Select

excel.Application.Run “SortMacro”
#---------------------------------------------------------------------------

This will work.

You can even define a macro on the fly if required.
See Microsoft Support

HTH,
– shanko