Ruby/ole excel delete row?

ruby-talk
1

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

2

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

3

Eric Ching 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

Hi,

Try this:

array=
worksheet1.Range("A1:L40").each do |cell|

  if cell.Value=~/A/
  ad=cell.Address
  array=ad.scan(/\w+/)
  column,row=array
worksheet1.Rows("#{row}:#{row}").Delete("Shift"=> Excel_constants::XlUp)
end
end

···

--
Posted via http://www.ruby-forum.com/\.

4

Eric Ching 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

Hi, I too stuck on the same page.
I want to iterate the data to read username, password from row2(from
same row) in excel. I am very much thankful if anybody helps me...I
couldn't find proper solution in my search, so posting here.

1 Field1 Value Field1 Value1 Value2 Field2 Value1
Value2
2 Url http://gmail.com username john John1 password pwd1
pwd2
thanks,
venkat

···

--
Posted via http://www.ruby-forum.com/\.

5

Eric Ching 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

(2..305).each do |row|
  if worksheet.Range("H#{row}").Value == "blah"
    worksheet.Rows(row).Delete
    redo
  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.

David

···

--
Posted via http://www.ruby-forum.com/\.

6

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
>

7

Simple version:

worksheet1.Range("A1:L40").each do |cell|

   if cell.Value=~/A/
      column,row=cell.Address.scan(/\w+/)
      worksheet1.Rows("#{row}:#{row}").Delete("Shift"=>
Excel_constants::XlUp)
  end

end

Li

···

--
Posted via http://www.ruby-forum.com/.

8

I have found it helpful to browse the VBA help modules for the various
MS Office applications. All the methods in those help modules are
mapped to the win32ole ruby methods. You can find the help modules
here

C:\Program Files\Microsoft Office\Office10\1033
or
C:\Program Files\Microsoft Office\OFFICE11\1033

Word => VBAWD10.CHM
Excel => VBAXL10.CHM
Access => VBAAC10.CHM
Outlook => VBAOL11.CHM
PowerPoint => VBAPP10.CHM

Blessings,
TwP

···

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.

9

thanks for your reply....but here this solution could not helps me
much...i need to run loops to the column data...below is my testdata
which is in a single lengthy row....
http://gmail.com pageTitle:Login user1 pwd1 user2 pwd2 user3 pwd3 DB1
DB2 DB3

I know my script is wrong, which was written as below...i want to use
xls class...

begin
    xlFile = XLS.new("Dir.getwd + '/TestData.xls")
    myData = xlFile.getColumnRecords("A1:AZ1", "Sheet1") # here i have
no idea whether to use getRange or getHash or getRowRecords or any
other.....
ensure
    xlFile.close
end
      myData.each do |r|
        puts r["user"]
        puts r["pwd"]
       # end
        end

...i want to iterate from user1:pwd1, user2:pwd2, user3:pwd3...and again
to iterate db1, db2, db3 like that...

your help is greatly appreciated....

thanks,
venkat

David Mullet wrote:

···

Eric Ching 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

(2..305).each do |row|
  if worksheet.Range("H#{row}").Value == "blah"
    worksheet.Rows(row).Delete
    redo
  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.

http://rubyonwindows.blogspot.com
Ruby on Windows: excel

David

--
Posted via http://www.ruby-forum.com/\.

10

you are a sick man!

-a

···

On Sat, 16 Dec 2006, Tim Pease wrote:

I have found it helpful to browse the VBA help modules <snip!!!>

--
if you find yourself slandering anybody, first imagine that your mouth is
filled with excrement. it will break you of the habit quickly enough. - the
dalai lama

11

I actually have had the flu for the past two days. How'd you know?

TwP

···

On 12/15/06, ara.t.howard@noaa.gov <ara.t.howard@noaa.gov> wrote:

On Sat, 16 Dec 2006, Tim Pease wrote:

> I have found it helpful to browse the VBA help modules <snip!!!>

you are a sick man!