Win32OLE Excel Cell index

[jschro02]

Okay,

So I'm wanting to dump some arrays into excel. The problem I am having is with the Cells object. Basically, this works (Returns "$F$5"):

row = 1; column = 0
first = worksheet.Cells(5,6).Address

Where this returns a 'method_missing: Cells' Win32OLERuntimeError error:

row = 1; column = 0
first = worksheet.Cells(row,column).Address

Can anyone tell me why the second example using variables does not work?

Various examples I have found while 'googling' show the use of variables, but I am unable to make it work on my machine.

Any help is always appreciated.

Thanks




Below is the full script for what it may be worth. I realized there are some things which I could do a little nicer, but one thing at a time.

require 'win32ole'
 
application = WIN32OLE.new('Excel.Application')
 
application.visible = TRUE
workbook = application.Workbooks.Add();
worksheet = workbook.Worksheets(1);
worksheet.visible
 
list = [['dog', 'cat', 'human', 'car','bathroom'],['again', 'another', 'list'],['just','an','array','of','nothing','in','particular']]
 
#read an array of arrays back into an excel document
row = 1; column = 0
list.each do |array|
 array.each do |element|
  worksheet.Range("A1").offset(row,column).value = element #.offset(row,column)
  column += 1
end
row += 1  
column = 0
end  
 
first = worksheet.Cells(5,6).Address
p first

[ParkinT]

I don't have any experience with this particular library. But, in your situation, I would try these things:

  first = worksheet.Cells(row.to_i, column.to_i).Address

or

  first = worksheet.Cells(:row, :column).Address

[jschro02]

Well,

converting to_i didn't work.

I tried using symbols, but I'm not sure if I did it correctly. I don't know if there's anything special I need to do to do to use :row, :column. I'm not very familiar with using symbols in this way.

Thanks

[Fenrir2]

Passing symbols certainly isn't the right solution .

row = 1; column = 0
first = worksheet.Cells(row,column).Address

This should behave exactly the same as:

first = worksheet.Cells(1,0).Address

i.e. replacing the variables with their values shouldn't change the behavior.

So if worksheet.Cells(1,0) doesn't work but worksheet.Cells(5,6) does, there's something strange happening. Or you're not allowed to pass 0? That could be your problem: maybe excel counts from 1 and not from 0. So the top-left cell is 1,1 and not 0,0.

This code:

row = 1; column = 0
list.each do |array|
 array.each do |element|
  worksheet.Range("A1").offset(row,column).value = element #.offset(row,column)
  column += 1
end
row += 1  
column = 0
end

Is probably inserting values in the second row (it leaves the first row empty?), because you're using offset() here (and because you set row=1 at the top).

[jschro02]

Fenrir2, you were right.

Excel apparently starts counting from 1, not 0. When I changed the beginning column number from :

column = 0 
to
column = 1,

I was able to fill the values into excel like I wanted.

It's so obvious I feel just a little dumb for not having thought of it on my own.

Once again, thanks for the help!!