Sort Excel spreadsheet with Ruby?

[Moran, Craig M (BAH)]

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

 

[Kent Dahl]

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

 

[Alan Chen]

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

 

[James Toomey]

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)

 

[Shashank Date]

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 http://support.microsoft.com/default.aspx?scid=kb;EN-US;194611

HTH,
-- shanko