Hey, I have a particular problem: I have these cells that use a custom formula to get a value and one cell that uses

a predefined formula. All these cells output correct value in excel. But when I grab the value of the cells, only the one with predefined formula returns the correct text value; the rest return sth like:

Win32::OLE::Variant=SCALAR(0xe0dabc)

what am I doing wrong? this is my code:

use warnings;
use strict;
# we are going be working with certain OLE
use Win32::OLE qw(CP_UTF8);
use Win32::OLE::Const 'Microsoft Excel';

use utf8;
# use Win32::OLE::Enum;

use CGI; # we'll be having some HTML writing
use CGI qw(:standard);
use CGI::Pretty qw( :html3 );
use diagnostics;

my $text = "";
my $sheet;
my $doc;
my $book;
my $language = "";
my $directory = "../maps update";
my $output_dir = "$directory/new_footers";
my $root = "C:\\{hidden for security reasons}";# input/output operatio
+ns work
my @values = ();

# CGI Intializing for HTML Output
my $q = CGI-> new;
$q -> header(-charset=>'utf-8'); # create header

opendir (DH, $directory) || die "can't opendir $directory: $!";
# test whether the item returned by grep is a file and its name does n
+ot start with "_"
# because there is one redirect input which start with "_"
my @dir_list = grep { (/^EN-US_.+/) && -d "$directory/$_" } readdir(DH
+); 

# we are working with Excel application
my $excel = Win32::OLE->new('Excel.Application', 'Quit');
$excel -> {DisplayAlerts} = "False"; # This turns off the "This file a
+lready exists" message.
$excel -> {Visible} = 0;

foreach (@dir_list){
    
    # extract the language abbreviation
    m/^EN-US_(.+)$/i;
    
    $language = lc($1);
    print "$language\n";
    
    $doc = "$root\\EN-US_$1\\cvg_$language.xlsx";
    
    $book = $excel ->Workbooks->Open("$doc") || die("Unable to open $d
+oc ", Win32::OLE->LastError());
    
    # Make Excel handle UTF8.
    $Win32::OLE::CP = CP_UTF8;

    # get sheet #3
    $sheet = $book->Worksheets("Sheet3");
    $sheet -> activate();



    #$text    = $sheet -> Range("A4")->{Value};
    #print "**$text**\n";
    
    
    # Prepare OUT_FOOTER file
    open(OUT_FOOTER, "> :utf8", "$output_dir/footer_$language.html") |
+| die("can't open $output_dir/footer_$language.html for writing: $!")
+;
    binmode OUT_FOOTER, ":utf8";
    
    # Mappings I have form excel file: 

     # A1 and A4 is are sentences
    # A4 : Currently Available in Production:
    # (A5,B5) (A9,B9) ... (A97, B97) point to (continent, countries)
    # A111 and A113 are additinal sentecnes
    
    $values[0]  = $sheet -> Range("A1")->{Value};
    $values[1]    = $sheet -> Range("A4")->{Value};
    $values[2]  = $sheet -> Range("A5")->{Value};
    $values[3]  = $sheet -> Range("B5")-> value();
    $values[4]    = $sheet -> Range("A9")->{Value};
    $values[5]  = $sheet -> Range("B9")->{Value};
    $values[6]  = $sheet -> Range("A13")->{Value};
    $values[7]  = $sheet -> Range("B13")->{Value};
    $values[8]    = $sheet -> Range("A59") ->{Value};
    $values[9]  = $sheet -> Range("B59")->{Value};
    $values[10] = $sheet -> Range("A62")->{Value};
    $values[11] = $sheet -> Range("B62")->{Value};
    $values[12] = $sheet -> Range("A69")->{Value};
    $values[13] = $sheet -> Range("B69")->{Value};
    $values[14] = $sheet -> Range("A97")->{Value};
    $values[15] = $sheet -> Range("B97")->{Value};
    $values[16] = $sheet -> Range("A111")->{Value};
    $values[17] = $sheet -> Range("A113")->{Value};
    
    print OUT_FOOTER $q->start_html("Maps - Coverage for $language"), 
+# start the HTML
                     $q->p($values[0]),
                     $q->p($values[1]), 
                     $q->p(b($values[2]) .  ": " . $values[3]),
                     $q->p(b($values[4]) .  ": " . $values[5]),
                     $q->p(b($values[6]) .  ": " . $values[7]),
                     $q->p(b($values[8]) .  ": " . $values[9]),
                     $q->p(b($values[10]) . ": " . $values[11]),
                     $q->p(b($values[12]).  ": " . $values[13]),
                     $q->p(b($values[14]) . ": " . $values[15]),
                     $q->p(b($values[16])),
                     $q->p(b($values[17])),
                     $q->end_html; 
        
    binmode STDOUT, ":utf8"; # necessary to battle the wide charachter
+ input warning
    $text = "$values[2] : $values[3]\n";
    print $text;
    
    # close document and Word instance
    print "Closing document and Excel\n";
    
    $book -> Save;
    undef $book;
    close OUT_FOOTER;
}
undef $excel;
closedir DH;

[download]

In reply to Excel OLE cell value custome pre-defined predefined function formula error by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.