Excel and Ruby

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

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

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

Thank you very much! :slight_smile: 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.

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.

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

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

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

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

by
TheR

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 :slight_smile:

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

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. :slight_smile:
I have just started programming. This is month 2 for me with any
language so the more projects I get the better.

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 methodValue’: )
(WIN32OLERuntimeError)
OLE error code:800A03EC in

HRESULT error code:0x80020009
Exception occurred.
from ReadingExcel.rb:16:in `’

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

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 had a chance to try your suggested format for the
worksheet.Range method. Thanks for the suggestion.

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 ‘paper’
reports.
Examples of doing this seems pretty scarce.

Regards, BevJ

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 this example) 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.

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.

~ 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 :slight_smile: <3 Ruby

Hope this explains it better :slight_smile: 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.

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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs