Ruby/win32ole Excel Move problem

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

Craig Moran wrote:

I have been trying to implement this Excel subroutine in Ruby,

So where’s your Ruby code? That will help us help you.

but am having trouble with the Move command.

I am having trouble finding out what trouble you are having.

The real issue is with the
“after:=Sheets()” portion of the command in the code below.

What is the real issue, specifically?

Does anyone know how to get around this using Ruby and win32ole?

Get around what, please?

This is what the code looks like in Excel VBA. All it does is sort the
sheets of an Excel workbook by name.
Thanks!

You’re most welcome.

  Worksheets(i).Move after:=Sheets(i + 1)
End If

Next i
Next x

End Sub

Ruby code? By the way, the listed sort routine is an exceptionally
inefficient bubble sort. I strongly recommend that you read all the
sheet
names into Ruby, sort them in Ruby, and write them out again. This would
most likely be faster than using the example above as a prototype,
especially as the number of data items increases.

Craig Moran wrote:

Sub Sheet_Sort()
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

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 wrote:

(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
}
}

If there are only a few worksheets, the fact that it’s a bubble sort
probably won’t matter in any practical sense.

On 9/21/06, Paul L. [email protected] wrote:

The real issue is with the
“after:=Sheets()” portion of the command in the code below.

This may be of some help. Specifically the C# example code. It looks
as
the move method takes two parameters (before, after). Try passing nil
for
before if you want to use after as you cannot use both before and after
combined…

http://msdn2.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.move.aspx

What is the real issue, specifically?

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 wrote:

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.

Okay, then. If all the worksheet names are unique, simply read all the
worksheet names, then sort them in Ruby:

array.sort.reverse

“reverse” because the sheets will be inserted at the beginning of the
stack,
which means the last item in the array ends up in the first position.

Then write a routine that moves them in sort order to the beginning of
the
worksheet stack.

array.each do |sheet_name|

move each sheet from wherever it is now to the beginning of the stack

end

This would be easier to understand later on, and it’s more efficient as
the
number of worksheets increases. Also, because you are presently moving
the
worksheets as the sort proceeds, it is much slower than simply sorting
the
names, which increases the burden created by the bubble sort.

The name sort would be performed at high speed, then the sheets would be
moved just once at the end of the sort. Much faster, and less
failure-prone.

Umm, on re-reading your post, I must ask whether the sheets can be
referred
to by name. If not, this becomes a bit more difficult, but it is still
feasible.

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!

Craig Moran wrote:

1.upto(xl.sheets.count) { |x| sheet_array<<xl.sheets(x).name }
sheet_array.sort!.reverse!

Slightly faster:

sheet_array.sort.reverse!

Only the result needs to be applied to the calling object.

sheet_array.each { |x| xl.sheets(x).move xl.sheets(1) }

Just a quibble:

sheet_array.each { |name| spreadsheet.sheets(name).move
spreadsheet.sheets(1) }

More self-documenting.

Thanks again for the help, Paul!

You are very welcome. It’s not every day that I get to walk the
untested,
hand-waving, theoretical high-wire. I much prefer to test before
posting,
but this time I couldn’t. :slight_smile:

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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs