From: Craig Moran <craig.m.moran@...>
Date: 2006-09-23T01:25:16+09:00
Subject: Re: ruby/win32ole Excel Move problem

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