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

Discussion in 'Perl Misc' started by pdfella, Dec 13, 2006.

  1. pdfella

    pdfella Guest

    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.
     
    pdfella, Dec 13, 2006
    #1
    1. Advertising

  2. Re: How to get an Excel cell value that's based on a function using

    pdfella wrote:
    > 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.

    --
    brian
     
    Brian Helterline, Dec 14, 2006
    #2
    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. Richard S Beckett

    Win32-OLE excel cell reference.

    Richard S Beckett, Aug 8, 2003, in forum: Perl Misc
    Replies:
    4
    Views:
    288
    Bob Walton
    Aug 12, 2003
  2. Lance Hoffmeyer
    Replies:
    0
    Views:
    250
    Lance Hoffmeyer
    Nov 17, 2003
  3. Replies:
    1
    Views:
    854
  4. aminnis
    Replies:
    1
    Views:
    144
    Brian Helterline
    Sep 16, 2004
  5. Replies:
    5
    Views:
    174
    Ben Morrow
    May 16, 2006
Loading...

Share This Page