Getting data from a variable number of columns in Excel using Perl with OLE

Question

I am using OLE with Perl to build a few charts in Excel. The data is in four different worksheets depending on which of the four tests it is for. The problem is that I don't know how to set the chart to look at all of the data when I don't know how long it will be.

I know to use;

$last_col = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByColumns}) -> {Column};

To get the last column with data in it. But I can't go

my $Range = $Sheet->Range("a1:$last_col3");

To set the range (obviously)

Any ideas how to set the range to include to the last column? Or would it be best just to add each column to the chart individually?

Thanks!

Answer 1

Ok, I've found a way. Not sure how good this is, but it works.

Instead of putting the variable into the range value, name the last cell which is interesting:

$sheet -> Cells ($last_row,$last_col) -> {Name} = "LastCell";

And then use this in the range field;

my $Range = $Sheet->Range("a1:LastCell");

Not the most elegant bit of coding I've every performed, but it works.

Answer 2

There's no reason why you shouldn't be able to use

$sheet->range( "A1:${last_col}3" )

It works for me.