I am looking for ways to speed up the searching for words in Excel. Currently, I simply loop through the used cells range and examine each cell's contents. For some spreadsheets this can take almost a minute if they have hunreds of columns and thousands of rows and dozens of sheets. When I have thousands of documents, this is too long.

I had a similar problem in Word where I had to search thousands of paragraphs in thousands of documents. What I did when I had only words to search for (not regexen) is use Word's Find function. This sped things up by an order of magnitude. I was wondering if anyone knew the syntax for Excel Finds. It is not the same as Word's. I have tried to translate the VBA that does work into Perl, but have failed thus far.

I currently search with something like this:

use strict;
use Win32::OLE;

use Win32::OLE::Const 'Microsoft Excel';
my $Excel = Win32::OLE->new('Excel.Application','Quit') or die "Cannot
+ open Excel\n";
my $Book = $Excel->workbooks->Open(FileName => "foo.xlsx", ReadOnly =>
+ 1) or die "Cannot open Spreadsheet\n";
my $num_sheets = $Book->Sheets->Count;
for my $sheet_num ( 1 .. $num_sheets ) {
    my $Sheet = $Book->worksheets( $sheet_num );
    my $max_col = $Sheet->UsedRange->SpecialCells( xlCellTypeLastCell 
+)->Column;
    my $max_row = $Sheet->UsedRange->SpecialCells( xlCellTypeLastCell 
+)->Row;
    for my $row ( 1 .. $max_row ) {
        for my $col ( 1 .. $max_col ) {
            print "I found it in Sheet $sheet_num Cell $row,$col\n" if
+ ( $Sheet->Cells( $row, $col )->Value =~ /cabbage/ );
        }
    }
}
[download]

The code that uses Excel's Find in VBA is

Any Ideas? I have tried things like my $loc = $Sheet->UsedRange->Cells->Find({What}=>"cabbage") and variations to no avail.

As always, I hope I have typed this in correctly. Please excuse any typos. And I cannot use any CPAN.