Angus has asked for the wisdom of the Perl Monks concerning the following question:

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.

Replies are listed 'Best First'.
Re: Handle Excel cells with the value #N/A in Perl
by davies (Monsignor) on Mar 28, 2010 at 15:04 UTC
    In Excel VBA, you can call any Excel function (even if it doesn't exist in VBA) via the incantation Application.function(arguments). By extension, therefore, you can access the ISNA function from Perl via something like $oExcel->isna(reference). The following code returns 1 if A1 is #NA: 
    use strict;
use warnings;
use diagnostics;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
my $wb = $xl->Workbooks->Open("c:\\data\\na.xls");
exit 0 if not defined($wb);
print $xl->isna($wb->Sheets(1)->Cells(1,1)->Value);

    [download]
    Regards,

    John Davies
[reply]
[d/l]
[select]
Re: Handle Excel cells with the value #N/A in Perl
by ZlR (Chaplain) on Mar 28, 2010 at 08:24 UTC
[reply]
Re: Handle Excel cells with the value #N/A in Perl
by jand (Friar) on Mar 29, 2010 at 07:03 UTC
    Loading Win32::OLE::Variant changes the way Variants are being displayed because it enables overloading of Win32::OLE::Variant objects, forcing a conversion to VT_BSTR type whenever the Variant is being stringified.

    The value -2146826246 is actually 0x8000A07FA interpreted as a signed value. If you take it as an unsigned value, then it is 2148141050, which matches the value Excel is supposed to store in a VT_ERROR variant for #N/A.

    Checking the Variant type from Perl should work like this: 

    if (ref($var) eq "Win32::OLE::Variant" && $var->Type == VT_ERROR) {
    ...
}

    [download]
[reply]
[d/l]
[select]

      Excellent, thank you.

[reply]
Re: Handle Excel cells with the value #N/A in Perl
by Angus (Novice) on Mar 28, 2010 at 15:50 UTC

    Thanks much for both replies. The point about "hardcore" is well taken, and the solution using Excel's @ISNA will work great and it will be useful in general to remember that Excel functions are available to me. Thanks again.

    I solved the immediate problem today by using Perl's ref function in a test of each cell's value. I am not expecting any references in the cells coming from my spreadsheets. As usual, I don't know why I didn't think of that approach yesterday.

    As part of my education into the ways of Perl and programming in general, I do wish I understood more specifics about why #N/A causes the result in Perl that it does. But for now, I'm moving on.

[reply]

Back to Seekers of Perl Wisdom