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
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
···
On Wed, Oct 13, 2010 at 3:19 AM, Dan Sr. <djonavarro@gmail.com> wrote:
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
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
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.
from ReadingExcel.rb:16:in `<main>'
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.?
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.
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.
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.
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
···
On Oct 13, 1:37 pm, Daniel Navarro <djonava...@gmail.com> wrote:
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
On Wed, Oct 13, 2010 at 3:19 AM, Dan Sr. <djonava...@gmail.com> wrote:
> 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
this will loop through i or the range of 1-100. or whatever you want to loop
to.
···
On Fri, Nov 5, 2010 at 10:20 AM, Bev Jennings <bhjdownload@mweb.co.za>wrote:
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.
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.
···
On Thu, Oct 14, 2010 at 1:19 PM, dtolj <dejan1@gmail.com> wrote:
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
On Oct 13, 1:37 pm, Daniel Navarro <djonava...@gmail.com> wrote:
> 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
>
>
>
> On Wed, Oct 13, 2010 at 3:19 AM, Dan Sr. <djonava...@gmail.com> wrote:
> > 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
>
> > --
> > Posted viahttp://www.ruby-forum.com/.
>
> --
> Daniel Navarro
> 9035612340
~ *****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.
···
On Fri, Nov 5, 2010 at 2:12 PM, Daniel Navarro <djonavarro@gmail.com> wrote:
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.
On Oct 14, 2:23 pm, Daniel Navarro <djonava...@gmail.com> wrote:
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.
On Thu, Oct 14, 2010 at 1:19 PM, dtolj <dej...@gmail.com> wrote:
> 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
> On Oct 13, 1:37 pm, Daniel Navarro <djonava...@gmail.com> wrote:
> > 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
> > On Wed, Oct 13, 2010 at 3:19 AM, Dan Sr. <djonava...@gmail.com> wrote:
> > > 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
puts cellcontentsA
puts cellcontentsB
Sorry about that. Was looking at some other code and copy pasted that into
the value ; ; Yay me.
···
On Fri, Nov 5, 2010 at 6:05 PM, Daniel Navarro <djonavarro@gmail.com> wrote:
~ *****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.
On Fri, Nov 5, 2010 at 2:12 PM, Daniel Navarro <djonavarro@gmail.com> > wrote:
> 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.
>
>
>
>
