Thanks to shanko, alan, and james for responding with enough insight to get
me going. I appreciate the help. Excel macros can be agonizingly slow and
this will help me in getting some VBA converted to Ruby and, thus, speed
things up on my end.
Craig
···
-----Original Message-----
From: jamesvtoomey@yahoo.com [mailto:jamesvtoomey@yahoo.com]
Sent: Wednesday, April 14, 2004 7:39 PM
To: ruby-talk@ruby-lang.org
Subject: Re: Sort Excel spreadsheet with Ruby?
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)