Sort Excel spreadsheet with Ruby?

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. :slight_smile:
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鈥檛 know the value of these constants
because they鈥檙e 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鈥檛 think
Ruby uses named arguments; that means you can鈥檛 say 鈥淗eader:=xlNo鈥.
The third problem is that 鈥淪election鈥 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(鈥淎1鈥))
However, that uses all the default values. I鈥檓 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鈥檚 the Sort routine:
鈥ort(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(鈥淎1鈥), 1, 鈥樷,鈥欌,鈥欌,鈥欌,鈥欌,2, 1, False,
1)