ruby/win32ole Excel Move problem

[Craig Moran]

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

 

[mully]

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

 

[Craig Moran]

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

 

[Craig Moran]

Paul-
I appreciate your input on this and agree with you on the low impact of
the bubble sort. I have two things in my favor:

1) I'm not making Excel visible (but I did in the example), so screen
updating will not slow this down at the GUI level in my production
code.
2) Excel's worksheet limitation is 255, so this type of sort shouldn't
be too negatively impacted with additional sheets.

Regardless, I'd like to know how someone else would implement this in a
more Rubyish manner. Keep in mind two things:

1) The Move function always places the moved worksheet *before* the
target worksheet and not after.
2) Any moved worksheet will change the indexes of other worksheets.

Thanks again for the comments-
Craig

 

[Craig Moran]

This is a great approach! I'm going to give it a whirl. To answer
your question, referring to worksheets by name is definitely possible.

 

[Craig Moran]

Sorting the sheets in Ruby works great. Thought I'd share the code for
any other folks who wish to perform their Excel macros in Ruby.

require 'win32ole'

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

sheet_array = []
1.upto(xl.sheets.count) { |x| sheet_array<<xl.sheets(x).name }
sheet_array.sort!.reverse!
sheet_array.each { |x| xl.sheets(x).move xl.sheets(1) }

xl.activeworkbook.save
xl.quit
xl = nil

Thanks again for the help, Paul!