Excel range finding problem

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

When I use Excel 2010 on my PC the following gives me the last row and column that has data in a worksheet

$last_row = $worksheet -> UsedRange -> Find({What => "*", SearchDirect
+ion => xlPrevious, SearchOrder => xlByRows})    -> {Row};
$last_col = $worksheet -> UsedRange -> Find({What => "*", SearchDirect
+ion => xlPrevious, SearchOrder => xlByColumns}) -> {Column};
[download]

When a user runs this on another PC running Excel 2013 the following error occurs.
Can't use an undefined value as a HASH reference at first Perl line above, <FMIN> line 10.
What has to be done to find out why this error occurs and/or how can it be altered to given what is required?

Comment on Excel range finding problem Download Code

Replies are listed 'Best First'.
Re: Excel range finding problem by Corion (Patriarch) on Oct 05, 2015 at 13:31 UTC
Split up the chain of accesses into separate statements: `my $range = $worksheet -> UsedRange or die "No UsedRange found for $worksheet"; my $findresult = $range -> Find({What => "", SearchDirection => xlPre +vious, SearchOrder => xlByRows}) or die "No result found for ''"; my $row = $findresult -> {Row}; defined $row or die "No row found from $findresult"; my $col = $last_col = $findresult -> {Column}; defined $col or die "No col found from $findresult";` [download] This way, you don't have to climb down the chain to your search results twice even. Also, I think I remember that a Range object in VBA has properties that give you the coordinates of the lower right cell directly. Maybe extract it from `$range->{Address}`...	[reply] [d/l] [select]
Re^2: Excel range finding problem by merrymonk (Hermit) on Oct 05, 2015 at 13:52 UTC
Thanks - I will try that and see what happens.	[reply]
Re: Excel range finding problem by u65 (Chaplain) on Oct 05, 2015 at 13:16 UTC
What module are you using to read the Excel file?	[reply]
Re^2: Excel range finding problem by merrymonk (Hermit) on Oct 05, 2015 at 13:21 UTC
I 'use' the following modules `use Win32::OLE; use Win32::OLE::Const q(Microsoft Excel);` [download] Do you want to see more of the Perl that opens the spreadsheet?	[reply] [d/l]