SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Apr 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Win32OLE Excel Cell index

    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"):
    Code Ruby:
    row = 1; column = 0
    first = worksheet.Cells(5,6).Address


    Where this returns a 'method_missing: Cells' Win32OLERuntimeError error:
    Code Ruby:
    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.
    Code Ruby:
    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

  2. #2
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,345
    Mentioned
    192 Post(s)
    Tagged
    5 Thread(s)
    I don't have any experience with this particular library. But, in your situation, I would try these things:
    Code Ruby:
      first = worksheet.Cells(row.to_i, column.to_i).Address

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

  3. #3
    SitePoint Member
    Join Date
    Apr 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Passing symbols certainly isn't the right solution .

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

    This should behave exactly the same as:

    Code ruby:
    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:

    Code ruby:
    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).

  5. #5
    SitePoint Member
    Join Date
    Apr 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fenrir2, you were right.

    Excel apparently starts counting from 1, not 0. When I changed the beginning column number from :
    Code:
    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!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •