tamsam 0 Newbie Poster
#================================== 
#   Script:  Finding and Replace word in excel
#   
#=======================================
use Spreadsheet::ParseExcel;
use strict;
use Win32::OLE;
use warnings;
use Cwd;
use Win32::OLE qw(in with);
use Win32::OLE::Const;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;        # die on errors...


my $oExcel = new Spreadsheet::ParseExcel;
my $oBook1 = $oExcel->Parse('path/find.xls');

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit'); 
$Excel->{Visible}=0;
$Excel->{DisplayAlerts}=0;

# my $dir = getcwd;
# $dir =~ s/\//\\\\/g;
# print "\n$dir";

my $Book = $Excel->Workbooks->Open("path/mainexcel.xls") or die "Can't open file"; 

print"\n\t________________________________________________";
print"\n\n\t\t Find and Replace word in Excel               ";
print"\n\t________________________________________________";

my (@find);
my $ch;

#reading data from find.xls-----------

 my($oWkS1, $oWkC1);
$oWkS1 = $oBook1->{Worksheet}[0];
for(my $row =$oWkS1->{MinRow}+1  ;$row<= $oWkS1->{MaxRow}; $row++)
{
    $oWkC1 = $oWkS1->{Cells}[$row][0];
    my $val1=$oWkC1->Value if($oWkC1);
    $val1=~s/^\s+|\s+$//;
    push(@find,$val1);
}
#________________________________Choice__________
my $i=1;

print"\n\tReplace Type";
print"\n\t-------------";
print"\n\t1.Matchcase\n\t2.NonMatchCase";
print"\n\t Enter your Choice:";
$ch=<>;
chomp($ch);
#-===================REPLACE Word Matching case
my $matchcase;

if($ch==1)
{
    $matchcase='True';
}
#-===================REPLACE Word Non Matching case
elsif($ch==2)
{
    $matchcase='False';
}
my $Sheets=$Book->Sheets;
my $Count = $Sheets->{Count};
print"\n $Count";

foreach my $findword(@find)
{
    $oWkC1 = $oWkS1->{Cells}[$i][1];
    my $rep=$oWkC1->Value if($oWkC1);
    print"\n $rep";
    #replace the word
    for(my $y=1;$y<=$Count;$y++)
    {
        my $sheet    = $Book -> Worksheets($y)->{Name};
        print"\n*****Sheet Name Is: $sheet";
        $sheet=$Book -> Worksheets($sheet);
        $sheet-> Activate;
        $Excel->Cells->Replace({What => "$findword",MatchCase => "$matchcase",Replacement   => "$rep"})

    }
    $i++;
}
#saving changes in the Excel Worksheet
$Book ->Save;
$Book->Close;

undef $ch;
undef $i;
undef $Book;
undef $oWkS1;
undef $oWkC1;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.