From: Nathan Smith <nlloyds@...>
Date: 2006-12-15T09:31:51+09:00
Subject: Re: ruby/ole excel delete row?

------=_Part_139649_2281669.1166142707921
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Ok, let me correct myself. This doesn't quite work. If you're in row 3 and
delete it, the row that was row 4 becomes row 3, but the iterator would go
to the next row, and skip the row you were in (try it so my explanation can
makes sense). A working way to do it might be to find the rows you want,
select them, then delete the selection. Like this:

a = ""
excel.range('h2:h305').each do |cell|
    a << cell.address + "," if(cell.value == 'hello')
end
excel.range(a.chomp(',')).entirerow.select
excel.selection.delete

This looks real ugly, so hopefully some more experienced rubyists can find a
much better way to do this.

Thanks,

Nate

On 12/14/06, Nathan Smith <nlloyds@gmail.com> wrote:
>
> You could use:
>
> excel.range('h2:h305').each do |cell|
>   cell.entireRow.delete if(cell.value == 'blah')
> end
>
> There's not a whole lot of documentation on win32ole ruby out there, but
> the
> Ruby Standard Library docs at http://ruby-doc.org are a good place to
> start,
> and there's a little bit in the PickAxe. If you're working with MS Office,
> I'd recommend going to Tools => Macro => Visual Basic Editor and looking
> through the object browser. You can also record macros, look at the VB
> source, and adapt that to Ruby.
>
> Thanks,
>
> Nate
>
> On 12/14/06, eching <bingopajama@hotmail.com> wrote:
> >
> > How do I delete a row in an excel spreadsheet with win32ole?
> >
> > I want to loop through some rows and delete a row if a cell in the row
> > contains a certain value.
> >
> > Here's the loop I am using:
> >
> > excel.Range("h2:h305").each do |cell|
> >   if cell.Value == "blah"
> >     # DELETE ROW HERE
> >   end
> > end
> >
> > And,where can I find documentation for using win32ols with excel.  I've
> > been looking but haven't found anything to my satisfaction.
> >
> > Thanks in advance,
> > Eric
> >
> >
> >
>
>

------=_Part_139649_2281669.1166142707921--