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
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
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.
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.?
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.
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.
~ 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
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.
Like I said I am new, I found an example this