I would put the mapping rules in another spreadsheet something like this

#!perl
use strict;
use Win32::OLE::Const 'Microsoft Excel';
Win32::OLE->Option(Warn => 3);

my $ex = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');

my $wb1 = $ex->Workbooks->Open("c:\\temp\\Book1.xlsx") ; 
my $wb2 = $ex->Workbooks->Add ; 
my $wb3 = $ex->Workbooks->Open("c:\\temp\\Mapping.xlsx");

my $sht1 = $wb1->Worksheets("Sheet1");
my $sht2 = $wb2->Worksheets("Sheet1");
my $sht3 = $wb3->Worksheets("Sheet1");
my $lastRow = $sht3->UsedRange->Find({What=>"*",
    SearchDirection=>xlPrevious,
    SearchOrder=>xlByRows})->{Row};

# Copy columns
for my $n (1..$lastRow){
  my $in  = $sht3->Range("A$n")->Value;
  my $out = $sht3->Range("B$n")->Value;
  print "Mapping rule $n : $in => $out\n";
  $sht1->Range("$in:$in")->Copy( $sht2->Range("$out:$out") );
}

$wb2->SaveAs("c:\\temp\\Book2.xlsx");
undef $ex;
[download]

Thank you! That is interesting idea. Having rules in a spreadsheet would allow for much easier organization of rule definition. I could have rules that would require multiple steps (each step would be a row - with say 1st column specifying grouping). I could easily make them as complex as I want by adding data to a rule in as many columns as needed and interpreting that data based on header. I guess XML would be another way of defining rules but spreadsheet is more visual.

for my $row (0..$lastrow) {
    for my $i (0..$#swap) {
    $Sheet2->Range($swap[$i][1].$row)->{'Value'} = $Sheet1->Range($swa
+p[$i][0].$row)->{'Value'}; 
    }
}
[download]

Thank you for your reply. That is roughly the approach so far. Identify the source and target column - make some sort of notation - very similar to your @swap array - and let it drive OLE operations. It is simple but somehow I feel I am not doing it right. Every time I need something more sophisticated I need another new class of "rule" and associated handler code. On a positive side that rule syntax is primitive enough that anybody can maintain the rules file.

I have not done a lot in XML but I thought that perhaps the way to go was to convert both XLS files to XML and then use something like XLST to manipulate target XML based on source one. Is it something that offers some inherent advantage or am I opening Pandora's box trying it?

If you're thinking about something like that, why not dump your data into a SQL database and access it that way? Perl has excellent SQL integration and SQL lets you slice and dice data any way you want. If you search on Perl SQL you will find all kinds of tutorials and guides.

Have a look here, I had to do something similar in the past and generic-ized it with a program with many options.

https://github.com/vinsworldcom/pests

Thanks a lot! Very helpful to have a working example and compare notes! I wrote a small library for myself - to do various basic tasks on Excel files and it grew over time to 18K lines but badly needs refactoring. Somewhere there I am doing similar things to your script but it will be VERY helpful to look at same problem from different perspective. And some things in OLE I struggled with. Thank you!

Mine could use some cleaning up too. But it works for what I needed, so never went back to clean it up.