I am reading Excel spreadsheets into Perl nested data structures. Some cells in my spreadsheet contain the value #N/A. When #N/A is converted into Perl via Win32::OLE I get Win32::OLE::Variant = Scalar(0x... various octal values). If I could get the literal #N/A, then I think I would have something that I could deal with (probably will set to undef). Any suggestions on how to get the literal #N/A from Excel to Perl, or other approaches to dealing with this value coming from spreadsheets?

Among other things, I tried reading documentation on Win32::OLE::Variant. I am a beginner and I need a little more hand holding than the document gave me. For example, I found a reference to a type() method in the POD of Variant.pm, but could not figure out how to make it work. (I thought I could test on the variant type and use it to handle this issue.) As part of my effort to use the type method, I added

use Win32::OLE::Variant;

to my code. I thought it interesting that that addition alone changes the Perl value of #N/A from being = Win32::OLE::Variant = Scalar(0x... various octal values) to being = -2146826246 (consistently), but am out of ideas on how to proceed.

The following code demonstrates the issue by creating an Excel workbook, writing 1 to cell D12 and #N/A to cell D13 (by this method #N/A appears as #N/A in Excel), then assigning that range to a reference to an array, and printing the array's values. 


#!/usr/local/bin/perl -

use warnings;
use diagnostics;
use strict;

use Win32::OLE::Const 'Microsoft Excel';
# use Win32::OLE::Variant;
        
$Win32::OLE::Warn = 3;

my $i = 0;

# Excel application object reference.
my $oExcel;

# Workbook object reference.
my $oWkBk;

# Sheet object reference.
my $oWkSht;

$oExcel = (Win32::OLE->GetActiveObject('Excel.Application')
            || Win32::OLE->new('Excel.Application', 'Quit'));

$oExcel->{Visible} = 1;

$oWkBk = $oExcel->Workbooks->Add();

$oWkSht = $oWkBk->Worksheets('Sheet1');
$oWkSht->Activate();

$oWkSht->Range('D12')->{Value} = 1;
$oWkSht->Range('D13')->{Value} = '#N/A';
my $arTestNA = $oWkSht->Range('D12:D13')->{Value};

$i = 0;
print("\n\t\t\$arTestNA->[$i][0] = ".$arTestNA->[$i][0]."\n\n");
$i = 1;
print("\n\t\t\$arTestNA->[$i][0] = ".$arTestNA->[$i][0]."\n\n");

$oExcel->{DisplayAlerts} = 0;
$oWkBk->Close({SaveChanges=>'false'});
$oExcel->{DisplayAlerts} = 1;
undef $oWkBk;
$oExcel->{WindowState} = xlMinimized;

__END__

[download]

The code above produces the following output. 

        $arTestNA->[0][0] = 1


        $arTestNA->[1][0] = Win32::OLE::Variant=SCALAR(0x1c0511c)

[download]

I am using ActiveState Perl v5.10.1 and Excel 2003 on Windows XP Home.

In reply to Handle Excel cells with the value #N/A in Perl by Angus

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.