ruby/ole excel delete row?

[eching]

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

 

[Li Chen]

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

 

[Li Chen]

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

 

[Tim Pease]

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.

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

 

[ara.t.howard]

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

you are a sick man!

-a

 

[Tim Pease]

you are a sick man!

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

TwP

 

[Venkat r m Reddi]

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

 

[David Mullet]

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
http://rubyonwindows.blogspot.com/search/label/excel

David

 

[Venkat r m Reddi]

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 user1wd1, user2wd2, user3wd3...and again
to iterate db1, db2, db3 like that...

your help is greatly appreciated....

thanks,
venkat