CODEWITHSUNDEEP
perlexcel-2010xlsxwin32ole
user1226409
user1226409

Reputation: 57

Problems parsing Excel 2010 .xlsx file with Win32::OLE in Perl

I have an excel file (.xlsx) connected to a "data source" with a .iqy file. I used Perl to to open the excel file and refresh the data. 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};

and cmd output 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 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};
####### EDIT: I initially didn't post this portion because it stops
#       before reaching it unless I make $LastCol and $LastRow constants
my @hasher;
my $c = "a";
for (my $cn=1; $cn <= $LastCol; $cn++){
    for (my $r=2; $r <= $LastRow; $r++){
# stops here with same error if I make $LastCol and $LastRow constants
        $hasher[$r-2]{ $Sheet->Range($c.'1')->{Value} } = $Sheet->Range($c.$r)->{Value};
    }
$c++;
}
####### end of EDIT
# Save as Excel
$Book->Save();  
$Book->Close();
$Excel->Quit();

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

Upvotes: 1

Views: 3756

Answers (3)

kaidong
kaidong

Reputation: 1

I think this is because one of you spreadsheets is empty. A better way to get the total column and row counts is:

my $Tot_Rows= $Sheet->UsedRange->Rows->{'Count'}; 

my $Tot_Cols= $Sheet->UsedRange->Columns->{'Count'};

Upvotes: 0

user1226409
user1226409

Reputation: 57

I wound up resolving the problem by adding a short pause to the code. The perl script was advancing before the excel sheet could finish refreshing. Thank you for your help.

Upvotes: 1

Pynner
Pynner

Reputation: 1017

I believe your find function is not returning a value now. I have two possible explanations.

1) Perhaps your new source data no longer contains "*" characters

2) The find function remembers some attributes between uses namely the LookIn, LookAt and SearchOrder. For best results, you should always set these 3 attributes when finding to ensure you get the functionality you want.

My adivice is to try executing the find manually on the spread sheet and see if it works. I don't think your error is in the OLE part of the script.

Upvotes: 1

Related Questions