Win32-OLE excel cell reference.

[Richard S Beckett]

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.

 

[Jay Tilton]

: 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 ;

 

[Bob Walton]

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.

 

[Richard S Beckett]

Richard S Beckett wrote:

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


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.

 

[Bob Walton]

....



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>