Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

FindNext function in OLE Excel

by esr (Scribe)
on Aug 28, 2009 at 15:44 UTC ( #791943=perlquestion: print w/replies, xml ) Need Help??
esr has asked for the wisdom of the Perl Monks concerning the following question:

I need to search through an Excel spreadsheet to find cells which contain long names in order to reduce the font size in those cells. I can do this using Find but I have been unable to get FindNext to work when trying to continue after the first one. Here is the code that works.

foreach $longname (@longnames) { $findit = $sheet->Range("A$first_data_row:$last_col$last_row")->Fi +nd({What=>$longname}); if ($findit) { $findit->Activate; $firstone = $excel->ActiveCell->{Address}; $fontsize = $excel->ActiveCell->Font->{Size}; $fontsize -= 2; $thisone = ""; $cnt = 0; while (($thisone ne $firstone) && ($cnt < 25)) { $thisone = $firstone if ($thisone eq ""); $newhash = $sheet->Range("$thisone")->Find({What=>$longnam +e}); $newhash->Activate; $thisone = $excel->ActiveCell->{Address}; $excel->ActiveCell->Font->{Size}=$fontsize; $cnt++; } } }
The $cnt stuff was inserted to prevent an infinite loop while I was debugging and is probably no longer needed.

Here is a small subset of some of the things I tried using FindNext instead of the Find in the while loop:

$newhash = $sheet->Cells->FindNext({After=>ActiveCell}); $newhash = $sheet->Cells->FindNext(); $newhash = $excel->Cells->FindNext({After=>ActiveCell}); $newhash = $sheet->Cells->FindNext({After=>"$thisone"});
I also tried using Range instead of Cells but I never found a combination that worked. All returned a null reference.

The code above works and I have no problem using it but I would like to know what I am doing wrong with FindNext.

Replies are listed 'Best First'.
Re: FindNext function in OLE Excel
by bobr (Monk) on Aug 29, 2009 at 14:36 UTC
    I tried playing with Find and FindNext methods of Excel and this seem to work:
    my $range = $wb->Sheets('Sheet1')->Range("B1:B10"); my $found = $range->Find({ What => 'John' }); my $firstfound = $found; do { warn $found->{Address}," = ",$found->{Value},"\n"; $found = $range->FindNext($found); } while($found && $found->{Address} ne $firstfound->{Address});
    It seems that FindNext have to be called upon the search range and provide last cell found by Find (a Range object). Unfortunately the FindNext cycle back to first item found, thus that condition in the while to break that. I had three Johns in my sample spreadsheet, so I got:
    $B$2 = John $B$4 = John $B$7 = John
    Also, there is no need to activate cell to change its properties, you use
    $found->Font->{Size} -= 2;
    -- Hope that helps, Roman

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://791943]
Approved by planetscape
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (8)
As of 2018-04-19 15:25 GMT
Find Nodes?
    Voting Booth?