Scrounging for WIN32OLE <--> MS-Excel Examples

Discussion in 'Ruby' started by dhtapp, Nov 10, 2003.

  1. dhtapp

    dhtapp Guest

    Hi,

    I've suddenly developed an interest in populating an Excel worksheet from
    within a little simulation I'm running.

    I've Googled for docs & examples on using the WIN32OLE class, and been able
    to cobble together some basic functionality with the help of what I've found
    (plus Dave & Andy's book), but I haven't been able to branch out much.

    I've also tried recording a couple of basic Excel macros and examining the
    output. Unfortunately, the number of hours I've spent in VB can be counted
    on a dog's left paw, and I'm not having much luck translating the syntax
    into stuff that WIN32OLE recognizes.

    And, I've perused Microsoft's tech site. For instance, they list multiple
    ways that Excel ranges can be accessed, including a simple 2d index (i, j),
    which would be perfect. But so far, I can only get the standard
    "A1:A5"-style notation to work.

    Does anyone know of some docs or guidelines posted somewhere that I may have
    missed?

    Thanks,

    - dan

    PS I know there's a specialized Excel library to write worksheets directly,
    but that appears to be semi-broken under 1.8, and I'm not nearly experienced
    enough to know how to patch it yet.
     
    dhtapp, Nov 10, 2003
    #1
    1. Advertising

  2. In <vQErb.11593$7B2.11385@fed1read04> dhtapp wrote:

    > I've suddenly developed an interest in populating an Excel worksheet
    > from within a little simulation I'm running.


    > Does anyone know of some docs or guidelines posted somewhere that I
    > may have missed?
    >


    Hi,

    there's a page on the Rubygarden Wiki: http://www.rubygarden.org/ruby?
    ScriptingExcel
    (maybe I'll find the time to add to the page what I learned so far)

    A great resource for general and programming Excel knowhow is Chip
    Pearson's site, for addressing cells in a range, take a look at http://
    www.cpearson.com/excel/cells.htm

    Some examples of addressing cells by index:

    # supposed that "sheet" contains a reference to an Excel worksheet
    sheet.range("a1").item(2,1).value=5 # 5 goes to B1

    # or get cell A1 first
    range = sheet.range("a1")
    # and use that from now on
    range.item(3,1).value=6

    # this should also be possible
    sheet.cells(4,1).value=7


    Parameters are in the order: rowindex, columnindex.

    In VBA you can omit item and value, as they are somehow default
    properties:
    VBA: range("a1")(3,1)=5
    Ruby: range("a1").item(3,1).value=5

    Regards, Bernhard
     
    Bernhard Leicher, Nov 11, 2003
    #2
    1. Advertising

  3. dhtapp

    daz Guest

    "Bernhard Leicher" <> wrote:
    >
    > # this should also be possible
    > sheet.cells(4,1).value=7
    >


    Not my question, Bernhard, but thanks for the reply.

    I spent some time looking at the problem (thinking that
    it should be fairly simple) and got nowhere.


    Now ...
    # Multiplication Table without alphanumeric cell references:

    require 'win32ole'
    excel = WIN32OLE.new("Excel.application")
    excel.visible = true
    excel.Workbooks.Add()

    for row in (1..12)
    for col in (1..12)
    excel.cells(row, col).value = row * col
    end
    end


    OK, it was easy - with the right help :)


    daz
     
    daz, Nov 12, 2003
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Julius de Bruijn

    Excel file modification without win32ole

    Julius de Bruijn, Feb 19, 2006, in forum: Ruby
    Replies:
    3
    Views:
    164
    Dominic Sisneros
    Feb 22, 2006
  2. Masaki Suketa
    Replies:
    4
    Views:
    399
    Dave Burt
    Mar 27, 2006
  3. Craig Moran

    ruby/win32ole Excel Move problem

    Craig Moran, Sep 21, 2006, in forum: Ruby
    Replies:
    5
    Views:
    206
    Craig Moran
    Sep 22, 2006
  4. Li Chen

    win32ole and excel and copy

    Li Chen, Nov 8, 2006, in forum: Ruby
    Replies:
    2
    Views:
    154
    Li Chen
    Nov 8, 2006
  5. zxem
    Replies:
    1
    Views:
    252
Loading...

Share This Page