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.

In reply to Problems parsing Excel 2010 .xlsx file with Win32::OLE by mwarrior

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.