Craig Moran
9/21/2006 6:18:00 PM
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