How to get an Excel cell value that's based on a function using Win32::OLE

[pdfella]

In my spreadsheet, I have a cell whose formula is
=WORKSHEET(TODAY(),0).
I'm trying to grab that value using Win32::OLE. Everything works when I
have an instance of Excel already opened and I use GetActiveObject.
However, when I do it with a new instance, it doesn't work. The value I
get back is #NAME?.

My script is as follows:

use strict;
use warnings;

use Win32::OLE;
use Win32::OLE::Variant;

my $excel = Win32::OLE->new('Excel.Application', 'Quit');
$excel->Application->{Calculation} = -4105; #Set to automatic
calculation

my $wbook = $excel->Workbooks->Open("Test.xls") || die "Error: $! \n";
my $sheet = $wbook->Worksheets("Sheet1");
$sheet->Range("A1")->Calculate(); #probably overkill since it's set to
auto calculation

my $cell = $sheet->Range("A1")->{'Value'};
print "$cell\n";


Any help would be appreciated.

 

[Brian Helterline]

In my spreadsheet, I have a cell whose formula is
=WORKSHEET(TODAY(),0).
I'm trying to grab that value using Win32::OLE. Everything works when I
have an instance of Excel already opened and I use GetActiveObject.
However, when I do it with a new instance, it doesn't work. The value I
get back is #NAME?.

My script is as follows:

use strict;
use warnings;

use Win32::OLE;
use Win32::OLE::Variant;

my $excel = Win32::OLE->new('Excel.Application', 'Quit');
$excel->Application->{Calculation} = -4105; #Set to automatic
calculation

my $wbook = $excel->Workbooks->Open("Test.xls") || die "Error: $! \n";
my $sheet = $wbook->Worksheets("Sheet1");
$sheet->Range("A1")->Calculate(); #probably overkill since it's set to
auto calculation

my $cell = $sheet->Range("A1")->{'Value'};
print "$cell\n";


Any help would be appreciated.

=WORKSHEET() is not a built-in Excel function (at least not on my
version of excel (v9) so I am guessing it comes from some add-in.

When you open a file using OLE, automatic macros, such as AutoOpen do
not execute. Maybe this is true for opening excel itself?

It's hard to say without knowing where/what =WORKSHEET() is.