Win32-OLE excel cell reference.

Discussion in 'Perl Misc' started by Richard S Beckett, Aug 8, 2003.

  1. Guys,

    I'm struggling with the syntax to point to the cells that I want to use in
    Excel.

    It seems that the Range command likes data like (A14), or (A14:B26), and I
    am happy with this.

    My problem occurrs when I only have numeric data to work with, because of
    incremented counters, or Count commands.

    I have had success with Cells(1,14), but I cannot for the life of me work
    out how to reference an area of cells like this...

    Cells(1,14:5,28)

    Can anyone help me with the correct syntax, please?

    Thanks.

    R.
     
    Richard S Beckett, Aug 8, 2003
    #1
    1. Advertising

  2. Richard S Beckett

    Jay Tilton Guest

    "Richard S Beckett" <> wrote:

    : I'm struggling with the syntax to point to the cells that I want to use in
    : Excel.
    :
    : It seems that the Range command likes data like (A14), or (A14:B26), and I
    : am happy with this.
    :
    : My problem occurrs when I only have numeric data to work with, because of
    : incremented counters, or Count commands.
    :
    : I have had success with Cells(1,14), but I cannot for the life of me work
    : out how to reference an area of cells like this...
    :
    : Cells(1,14:5,28)

    It's more of a question about the Excel object library than about
    Perl.

    The Range() property can accept two Range objects for its arguments.
    "Cells(1,14)" and "Cells(5,28)" would return two such Range objects.

    Written in Excel VBA, it would go like

    Range( Cells(1,14), Cells(5,28) ).some_method

    Written in Perl, it would go like

    $worksheet->Range(
    $worksheet->Cells(1,14),
    $worksheet->Cells(5,28)
    )->some_method ;
     
    Jay Tilton, Aug 9, 2003
    #2
    1. Advertising

  3. Richard S Beckett

    Bob Walton Guest

    Richard S Beckett wrote:

    ....> It seems that the Range command likes data like (A14), or (A14:B26), and I
    > am happy with this.

    ....


    > I have had success with Cells(1,14), but I cannot for the life of me work
    > out how to reference an area of cells like this...
    >
    > Cells(1,14:5,28)

    ....


    > R.



    Well, despite its plural name, the Cells property only returns a Range
    which is a single cell. You can do:

    ...Range(Cells(1,14),Cells(5,28))...

    The main reason for the Cells property is to permit the manipulation of
    a Range given row and column index numbers, rather than strings like
    "B3:E7", which are a bit harder to manipulate (especially in VB).

    Use Excel's object browser to help look up stuff like that. Your
    question really isn't a Perl question, but, close enough I guess.

    --
    Bob Walton
     
    Bob Walton, Aug 10, 2003
    #3
  4. "Bob Walton" <> wrote in message
    news:...
    > Richard S Beckett wrote:
    >
    > ...> It seems that the Range command likes data like (A14), or (A14:B26),

    and I
    > > am happy with this.

    > ...
    >
    >
    > > I have had success with Cells(1,14), but I cannot for the life of me

    work
    > > out how to reference an area of cells like this...
    > >
    > > Cells(1,14:5,28)

    > ...
    >
    >
    > > R.

    >
    >
    > Well, despite its plural name, the Cells property only returns a Range
    > which is a single cell. You can do:
    >
    > ...Range(Cells(1,14),Cells(5,28))...
    >
    > The main reason for the Cells property is to permit the manipulation of
    > a Range given row and column index numbers, rather than strings like
    > "B3:E7", which are a bit harder to manipulate (especially in VB).


    Great, just what I needed, thanks.

    > Use Excel's object browser to help look up stuff like that. Your
    > question really isn't a Perl question, but, close enough I guess.


    I don't understand. This is my biggest problem, I'm sure the information is
    available somewhere, but I have no idea where, or how to access it.

    Thanks.

    R.
     
    Richard S Beckett, Aug 11, 2003
    #4
  5. Richard S Beckett

    Bob Walton Guest

    Richard S Beckett wrote:

    > "Bob Walton" <> wrote in message
    > news:...
    >
    >>Richard S Beckett wrote:

    ....


    >
    >>Use Excel's object browser to help look up stuff like that. Your
    >>question really isn't a Perl question, but, close enough I guess.
    >>

    >
    > I don't understand. This is my biggest problem, I'm sure the information is
    > available somewhere, but I have no idea where, or how to access it.

    ....


    > R.


    <way off topic>

    To fire up Excel's object brower, first fire up Excel. Then hold the
    Alt key down and push the F11 key. Then click the icon that looks like
    a box with junk flying out of it, by default the third button from the
    right on the toolbar (mouseover gives tooltip "object browser"). Type
    in what it is you are looking for into the blank listbox. Under the
    "class" column, look for the parent of the entity you are looking for.
    Click on the member column. Brief usage information is shown at the
    bottom. Push the F1 key for documentation. The examples are usually
    the best help you will get. Then translate to Perl per the mechanistic
    formula in perldoc Win32::OLE. I have typically learned more about OLE
    by studying Perl apps from books and the web than I have by studying the
    MS docs.

    You can use the object browser provided with most OLE-aware Windoze apps
    to find out how to access their classes, members and properties.

    </way off topic>

    --
    Bob Walton
     
    Bob Walton, Aug 12, 2003
    #5
    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. M. David Allen

    Win32::OLE, Excel, and coloring

    M. David Allen, Jun 24, 2003, in forum: Perl
    Replies:
    1
    Views:
    5,452
    Jay Tilton
    Jun 25, 2003
  2. M. David Allen

    Win32::OLE, Excel, and coloring

    M. David Allen, Jun 24, 2003, in forum: Perl Misc
    Replies:
    1
    Views:
    201
    Jay Tilton
    Jun 25, 2003
  3. Lance Hoffmeyer
    Replies:
    0
    Views:
    273
    Lance Hoffmeyer
    Nov 17, 2003
  4. Replies:
    1
    Views:
    903
  5. pdfella
    Replies:
    1
    Views:
    183
    Brian Helterline
    Dec 14, 2006
Loading...

Share This Page