Ruby/win32ole Excel Move problem

ruby-talk
1

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

2

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.

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

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.

···

--
Paul Lutus
http://www.arachnoid.com

3

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

4

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.

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...

What is the real issue, specifically?

···

On 9/21/06, Paul Lutus <nospam@nosite.zzz> wrote:

> 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.

> 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

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.

--
Paul Lutus
http://www.arachnoid.com

Michael Guterl

5

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

6

Craig Moran wrote:

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

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

···

--
Paul Lutus
http://www.arachnoid.com

7

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

···

Paul Lutus wrote:

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

--
Paul Lutus
http://www.arachnoid.com

8

Craig Moran wrote:

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.

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.

···

--
Paul Lutus
http://www.arachnoid.com

9

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.

Paul Lutus wrote:

···

Craig Moran wrote:

> 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.

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.

--
Paul Lutus
http://www.arachnoid.com

10

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:

···

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.

Paul Lutus wrote:
> Craig Moran wrote:
>
> > 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.
>
> 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.
>
> --
> Paul Lutus
> http://www.arachnoid.com

11

Craig Moran wrote:

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!

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:

···

--
Paul Lutus
http://www.arachnoid.com