From: Paul Lutus <nospam@...>
Date: 2006-09-22T08:05:06+09:00
Subject: Re: ruby/win32ole Excel Move problem

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