Excel and Ruby

[Dan Sr.]

Hello all,

I'm just doing some data entry work and thought it would be fun to
automate my work with Ruby.(yes I could do this in VB but what fun is
that?)

What I am doing is putting column N into an Array. And then comparing
the array to an already specified array to get the value. If that value
is present then I want to put an X in column P-V, Time in w-x and dates
in y-z. so far I have got everything that I have coded to work correctly
but haven't put classes or any structure to it. Just wanted to see if
what I was doing was ruby~esque so far and if anyone had any cool tricks
or see something I am doing wrong. (My first program automating excel or
really anything in ruby) I have automated some files with tutorials but
this is my first attempt on automating anything for any relevant
purpose. It's only half 1/4 or 1/8 written so far.
My goal is to loop through the items one by one, incrementing +1 to the
next cell until I reach the end of the file.
if __FILE__ == $0
# TODO Generated stub
require 'win32ole'
xl = WIN32OLE.connect('Excel.Application')
wb = xl.ActiveWorkbook
ws = xl.ActiveSheet
ws.Range('N1').each do |cell|
ws.Range('P1:V1').each do |days|
ContentsN = cell.value
CellContentsN = ContentsN.scan(/\w+/)
Dayofweek = %w{Monday Tuesday Wednesday Thursday Friday Saturday
Sunday}
Month = %w{Janurary Feburary March April May June July August
September October November December}
01.upto(31){|Day|}
Comparedayofweek = Dayofweek & CellContentsN
Comparemonth = Month & CellContentsN
Compareday = Day & CellContentsN
end
end
end

 

[Daniel Navarro]

[Note: parts of this message were removed to make it a legal post.]

oops sorry I initialized alot of Constants there.

anyways here is my updated code

it works fine except it is returning multiple values of the result. Any
Ideas?

require 'win32ole'
xl = WIN32OLE.connect('Excel.Application')
wb = xl.ActiveWorkbook
ws = xl.ActiveSheet
ws.Range('N2').each do |cell|
ws.Range('P3501:V3501').each do |days|
contentsN = cell.value
cellContentsN = contentsN.scan(/\w+/)
dayofweek = %w{Monday Tuesday Wednesday Thursday Friday Saturday Sunday}
month = %w{Janurary Feburary March April May June July August September
October November December}
comparedayofweek = dayofweek & cellContentsN
comparemonth = month & cellContentsN

p comparedayofweek
p comparemonth

end
end

 

[dtolj]

Why do you have a nested loop?
You can do this in two ways
1) with activecell and offset to imitate moving around Excel sheet
with a cursor
2) or something like this:
line=3D1
while worksheet.range("q#{line}").value
worksheet.range("q#{line}").value
line=3Dline+1
end

 

[Daniel Navarro]

[Note: parts of this message were removed to make it a legal post.]

Thank you very much! Like I said I am new, I found an example this
morning like that but haven't had time to play with it. Again thank you so
much.

Why do you have a nested loop?
You can do this in two ways
1) with activecell and offset to imitate moving around Excel sheet
with a cursor
2) or something like this:
line=1
while worksheet.range("q#{line}").value
worksheet.range("q#{line}").value
line=line+1
end

 

[dtolj]

http://rubyonwindows.blogspot.com/search/label/excel

Thank you very much! Like I said I am new, I found an example this
morning like that but haven't had time to play with it. Again thank you s= o
much.

Why do you have a nested loop?
You can do this in two ways
1) with activecell and offset to imitate moving around Excel sheet
with a cursor
2) or something like this:
line=3D1
while worksheet.range("q#{line}").value
=A0worksheet.range("q#{line}").value
=A0line=3Dline+1
end

oops sorry I initialized alot of Constants there.
anyways here is my updated code
it works fine except it is returning multiple values of the result. A= ny
Ideas?
=A0 require 'win32ole'
xl =3D WIN32OLE.connect('Excel.Application')
wb =3D xl.ActiveWorkbook
ws =3D xl.ActiveSheet
ws.Range('N2').each do |cell|
ws.Range('P3501:V3501').each do |days|
=A0 contentsN =3D cell.value
=A0 cellContentsN =3D contentsN.scan(/\w+/)
=A0 dayofweek =3D %w{Monday Tuesday Wednesday Thursday Friday Saturda=

y
Sunday}

=A0 month =3D %w{Janurary Feburary March April May June July August S= eptember
October November December}
=A0 comparedayofweek =3D dayofweek & cellContentsN
=A0 comparemonth =3D month & cellContentsN
=A0 p comparedayofweek
=A0 p comparemonth
end
end

 

[Bev Jennings]

thanks for the info posted thus far. I am a new convert to Ruby after
much "selling" by my grandson. I was looking for a concise language
(like Fortran - that dates me hey!!) to automate some Excel procedures
that I do. Pretty simple stuff I guess if you know what you are doing.
Can any of you knock out the few lines of Ruby code to read a cell in an
Excel spreadsheet which I could then build on.? It would make an
enormous contribution to my learning curve.

 

[Jethrow ..]

First off, I'd recommend finding some VB examples for reference (the
Excel Macro recorders will help). Then, once you get a feel for the
Excel Object Model, scripting Excel is easy. Here's a simple example:

require "WIN32OLE"
xl = WIN32OLE.new("Excel.Application")
xl.visible = true
xl.workbooks.add
for cell in xl.range("A1:E10")
cell.value = rand(100)
end
xl.range("C3").select
puts xl.range("C3").value
gets

This should help too: http://msdn.microsoft.com/en-us/library/aa213696
- jethrow

 

[Damjan Rems]

If your table is not too complex you may look at
http://spreadsheet.rubyforge.org/. It works under Linux too.

by
TheR

 

[Bev Jennings]

Hi jethrow & TheR Thank u so much for the info - this will give me a
great start BevJ

 

[Bev Jennings]

Hi - the info that you pointed me to has been very good and following
the examples given I wrote the following code:
_____________________________________________________________
require 'win32ole'
data = Array.new

#Opening spreadsheets, accessing workbooks and worksheets

excel = WIN32OLE::new('excel.Application')
workbook =
excel.Workbooks.Open('d:\workgiga\ruby\programs\CVMATest.xls')
worksheet = workbook.Worksheets(1) #get hold of the first worksheet
worksheet.Select #bring it to the front

#reading data from spreadsheet

worksheet.Range('b3')['Value'] #displays value of single cell
_______________________________________________________________

When I run this interactively all seems to go well until I get to the
last line (trying now to get a value for a cell) I get an error message
basically saying that 'Value' is unknown and undefined. When I initially
wrote it using irb it worked once correctly and returned a value for
each cell as requested but since then I get the error message. If I run
it under SciTec I get the following error message:
____________________________________________________
ReadingExcel.rb:16:in `[]': (in OLE method `Value': )
(WIN32OLERuntimeError)
OLE error code:800A03EC in <Unknown>
<No Description>
HRESULT error code:0x80020009
Exception occurred.

Exit code: 1

_______________________________________________________
Line 16 referred to is:
worksheet.Range('b3')['Value']

Any ideas of what I am doing wrong and where maybe I can get some
additional documenetation on 'win32ole' where could decypher the error
code.?

 

[Dan Sr.]

worksheet.Range('b3')['Value']

Any ideas of what I am doing wrong and where maybe I can get some
additional documenetation on 'win32ole' where could decypher the error
code.?

Hello

just put
worksheet.Range('b3').value

Best place I have found for excel ruby info is
http://rubyonwindows.blogspot.com/2007/03/automating-excel-with-ruby.html

 

[Dan Sr.]

worksheet.Range('b3')['Value']

Any ideas of what I am doing wrong and where maybe I can get some
additional documenetation on 'win32ole' where could decypher the error
code.?

Let me know what you are trying to do. I would love to help you because
it's just more experience for me.
I have just started programming. This is month 2 for me with any
language so the more projects I get the better.

 

[Bev Jennings]

Hi,

Sorry about the delay in replying but I have been wandering around the
mountains in our area for a couple of days.

I have not yet a chance to try your suggested format for the
worksheet.Range method. I hope it works.

With regard to your question about what I am trying to do... The first
objective of my program is to bring me up to speed using Ruby program to
automate the extraction of data from Excel spreadsheets. Once I have got
that working then I will extract data from a large spreadsheet covering
levy payments by occupants in a retirement village and then use Ruby to
print out letters to individual occupants who are wanting to reconcile
their payments. This will then require using Ruby to print reports.
Examples of doing this seems pretty scarce.

Regards, BevJ

 

[Bev Jennings]

hi DanSr

Your suggested format works like a charm (thanks so much) so I am now
back in business and have succesfully extracted the info I needed from
the 9 cells in row 3. Now the next trick we need to apply our minds to
is how via a loop we can change the row number in the Range parameter in
the worksheet.Range('b3').value statement (i.e. 3 in the example you
sent to me) to say 6 or any other specified value. So that I can extract
the same 9 cells in row 6 that I did in row 3 and so on looping down to
cover the rows that I need. In other words do you think that we can use
a varible like rowno in worksheet.Range('b+rowno').value instead of 3 or
6.

 

[Daniel Navarro]

[Note: parts of this message were removed to make it a legal post.]

Not a problem, try interpolation with a for loop.

example

for i in 1..100 do

worksheet.Range("B#{i}").value


this will loop through i or the range of 1-100. or whatever you want to loop
to.

 

[Daniel Navarro]

[Note: parts of this message were removed to make it a legal post.]

~ *****With regard to your question about what I am trying to do... The
first
objective of my program is to bring me up to speed using Ruby program to
automate the extraction of data from Excel spreadsheets. Once I have got
that working then I will extract data from a large spreadsheet covering
levy payments by occupants in a retirement village and then use Ruby to
print out letters to individual occupants who are wanting to reconcile
their payments. This will then require using Ruby to print reports.
Examples of doing this seems pretty scarce.*****~

As far as extracting the data from an excel sheet, I'm assuming Names and
Levy payments and inserting into a report. What kind of report are we
talking about? Is it just text? A crystal report?

for i in 1..100 do
# Lets iterate of each of these values into an array
worksheet.Range("A#{i}").each do |cella|
worksheet.Range("B#{i}").each do |cellb|
#This could also be done without a loop by simply calling the range of cells
worksheet.Range("A1:A100").each do |cella|
worksheet.Range("B1:B100").each do |cellb|
=beginAnd make sure we are talking about text and values here. Assuming of
course cell a is names and cell b is a value or number. Value has to to do
with integers and floats. Integars are whole numbers where as floats extend
with past the decimal. 1 = integer 2.22 = float
=end

cellcontentsA = cella.text
cellcontentsB = callb.value

puts cellvaluea
puts cellvalueb

Array's come with their own special methods and you can see what the methods
are by typing in Array.methods Lots of cool toys in there <3 Ruby


Hope this explains it better There are so many ways to get the same
results, it's fun to look over new things and condense it to the most simple
form. This is not the most simple form but for learning purposes II hope it
does a good job explaining each element of the code for you.
If everything works out the way you want to let me know and we can get on to
making the Mailing letters and printing them out. Can do this in many ways
in either word, a text file, simply e-mail automatically. You name it. It's
there.

 

[Daniel Navarro]

[Note: parts of this message were removed to make it a legal post.]

eek and the last little bit should be

cellcontentsA = cella.text
cellcontentsB = callb.value

puts cellcontentsA
puts cellcontentsB
Sorry about that. Was looking at some other code and copy pasted that into
the value ; ; Yay me.