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

I have an excel file connected to a "data source" with a .iqy file. I used Perl to to open the excel file, refresh the data, and extract the data to be reformatted and output to some other text file. Initially, my code worked. However, I needed to change what .iqy file my spreadsheet was linked to, and doing this seems to have broken my Perl script (despite not actually changing anything inside the script itself). Now it fails when I call

my $LastRow = $Sheet->UsedRange->Find({What=>"*",
    SearchDirection=>xlPrevious,
    SearchOrder=>xlByRows})->{Row};

[download]

and cmd reads: "Can't use an undefined value as a HASH reference at sharing.pl line 20." I've tried debugging this, but I don't know enough about the guts of the Win32::OLE module to know how to even catch where or why the problem happens in the debugger. The source code for my script (except the portion that outputs to a text file) is:

#!/usr/bin/perl
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';

$Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
       Win32::OLE->new('Excel.Application');
$Excel->{'Visible'} = 0;        #0 is hidden, 1 is visible
$Excel->{DisplayAlerts}=0;    #0 is hide alerts

# Open File and Worksheet
my $Book = $Excel->Workbooks->Open ('C:\shareP\sp.xlsx'); # open Excel
+ file
$Sheet = $Book->Worksheets(1);

# Refresh Data (ActiveWorkbook.RefreshAll)
$Book->RefreshAll();

# Find Last Column and Row
my $LastRow = $Sheet->UsedRange->Find({What=>"*",
    SearchDirection=>xlPrevious,
    SearchOrder=>xlByRows})->{Row};

my $LastCol = $Sheet->UsedRange->Find({What=>"*", 
                  SearchDirection=>xlPrevious,
                  SearchOrder=>xlByColumns})->{Column};

# read values from each column

my @hasher;
my $c = "a";
for (my $cn=1; $cn <= $LastCol; $cn++){
    for (my $r=2; $r <= $LastRow; $r++){
        $hasher[$r-2]{ $Sheet->Range($c.'1')->{Value} } = $Sheet->Rang
+e($c.$r)->{Value};
    }
$c++;
}

# Save as Excel
$Book->Save();  
$Book->Close();
$Excel->Quit();

[download]

Thank you in advance for any and all advice. I'm really stuck on this one.

Replies are listed 'Best First'.
Re: Problems parsing Excel 2010 .xlsx file with Win32::OLE
by blakew (Monk) on Feb 22, 2012 at 18:16 UTC
    If you set Visible to 1 and pause the script after RefreshAll do you see any data? Find returns undef when nothing is there.
[reply]
[d/l]
[select]

      When I set visible to 1 and pause after RefreshAll() the excel sheet has all of its data. I also tried making 

      my $LastRow = $Sheet->UsedRange->Find({What=>"*",
    SearchDirection=>xlPrevious,
    SearchOrder=>xlByRows})->{Row};

my $LastCol = $Sheet->UsedRange->Find({What=>"*", 
                  SearchDirection=>xlPrevious,
                  SearchOrder=>xlByColumns})->{Column};

      [download]

      constants, but then the code gets stuck with the same error on

      $hasher[$r-2]{ $Sheet->Range($c.'1')->{Value} } = $Sheet->Range($c.$r)->{Value};

      However, in this second case, if I run it twice in a row, it works... the only difference I see between runs is that excel is still open from the first run because Perl didn't reach the 'close()' line, but this didn't fix the first case (without constants).

[reply]
[d/l]
[select]
        It's hard to tell exactly what's happening and I'm not familiar with web querying in Excel, but my guess is RefreshAll isn't totally blocking so your code continues executing even if it's not done. In the second run the data has completed the refresh and

        Win32::OLE->GetActiveObject('Excel.Application')

        grabs the background Excel process you started previously, which now has all of the data. Since you're quitting anyways I would remove this (just create a new instance) and try sleep-ing for a few minutes after RefreshAll. You may need a while(1) loop that checks on the status of the data query, or set the BackgroundQuery property to 0 on every object (if that's the root problem).

        Installing a signal handler to close and exit would also prevent Excel staying open in the background: 

        
$SIG{INT} = sub { cleanup(); die "Caught interrupt"; };

... (main code) ...

cleanup();

sub cleanup {
  $Book->Save();  
  $Book->Close();
  $Excel->Quit();
}

        [download]
[reply]
[d/l]
[select]
Re: Problems parsing Excel 2010 .xlsx file with Win32::OLE
by nemesdani (Friar) on Feb 23, 2012 at 10:15 UTC
    Is there anything in the worksheet in the first place? I had a similar problem and error message. It turned out that if a worksheet doesn't have anything in it (all cells are undef), Excel, or OLE, or whichever doesn't recognize the worksheet as an existing one. I also don't know OLE very well, so I'm just guessing.
[reply]

Back to Seekers of Perl Wisdom