From: Craig Moran <craig.m.moran@...>
Date: 2006-09-22T03:20:11+09:00
Subject: Re: ruby/win32ole Excel Move problem

mully, thanks for the kickstart.  You had the right answer.  Here it is
in Ruby (it's still a bubble sort):

require 'win32ole'

xl = WIN32OLE.new("excel.application")
xl['Visible'] = true
xl.workbooks.open("filename.xls")

sheets = xl.sheets.count
(sheets - 1).times {
  sheets.downto(2) { |i|
    if xl.sheets(i).name < xl.sheets(i - 1).name
      xl.sheets(i).activate
      xl.sheets(i).move xl.sheets(i - 1)
    end
  }
}

xl.activeworkbook.save
xl.quit
xl = nil

I hope this helps someone out in the future.
Warm Regards-
Craig

mully wrote:
> Craig Moran wrote:
> > I have been trying to implement this Excel subroutine in Ruby, but am
> > having trouble with the Move command.  The real issue is with the
> > "after:=Sheets()" portion of the command in the code below.  Does
> > anyone know how to get around this using Ruby and win32ole?
> >
> > This is what the code looks like in Excel VBA.  All it does is sort the
> > sheets of an Excel workbook by name.
> > Thanks!
> > Craig
> >
> > Sub Sheet_Sort()
> >
> > Dim shtCount As Integer
> > shtCount = Sheets.Count
> >
> > For x = 3 To shtCount - 1
> >   For i = 3 To shtCount - 1
> >     If Worksheets(i).Name > Worksheets(i + 1).Name Then
> >       Worksheets(i).Move after:=Sheets(i + 1)
> >     End If
> >   Next i
> > Next x
> >
> > End Sub
>
> Regarding moving the worksheet, this does the trick for me...
>
> wb.Worksheets(x).Move wb.Worksheets(y)
>
> ...where wb = the Workbook object, x = the index of Worksheet you wish
> to move, and y = the index position to which you wish to move it.
>
> For example, to move the third worksheet to the first position:
>
> wb.Worksheets(3).Move wb.Worksheets(1)
> 
> Hope that helps.
> 
> Mully