Ruby/ole excel delete row?

Ruby
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

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

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

4

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

5

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

6

On Sat, 16 Dec 2006, Tim P. wrote:

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

you are a sick man!

-a

7

On 12/14/06, Nathan S. [email protected] wrote:

through the object browser. You can also record macros, look at the VB
source, and adapt that to Ruby.

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

8

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

9

On 12/15/06, [email protected] [email protected] wrote:

On Sat, 16 Dec 2006, Tim P. wrote:

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

you are a sick man!

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

TwP

10

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

11

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