Member Avatar for Nole_diver
!/usr/bin/perl -w
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel'; 

$Win32::OLE::Warn = 3; # die on errors...

# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit'); 

# get a new workbook
$book = $ex->Workbooks->Open('C:\Users\Andrew Babitt\Documents\ProductClonesTest.xls');
$Obook = $ex->Workbooks->Open('C:\Users\Andrew Babitt\Documents\NSS\extract_Products.xls');

my $billschedule = $Obook->clumn(B)->{value};
my $totalamount = $book->clumn(D)->{value};
my $opnum = $book->clumn(E)->{value};

my @array = (\$billschedule, \$totalamount, \$opnum);

my $var = 0;

while ($book->Range(0,0)->{value} ne '')
{
if (@array[0][$var] = $book->Range(0,9)->{value})
    {
    if (@array[1][$var] = $book->Range(0,10)->{value})
        {
        write(@array[2][$var], $book->Range(0,11)->{value});
        }
    }
else
$var = $var+1;


}
  • 2 Contributors
  • 2 Replies
  • 331 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by wickedxter

Dani AI

Generated

Quick summary and a safe, practical approach for this task.

The goal is to read three columns from one workbook (the source), use the first two as a match-key, and — when both match a row in the second workbook (the target) — write the third value into the target. Common problems in the posted snippet are mixed/incorrect object names, incorrect Range/Cells usage, Perl scalar vs slice syntax, and using assignment where you meant comparison. A robust pattern is: read the source into an in-memory lookup (hash keyed by the first column), then loop the target rows and do fast hash lookups to decide what to write.

Key tips before the example:

  • Use Cells(row,col)->{Value} or Range("A1")->{Value}; Excel uses 1-based row/col indexes.
  • Range("B2:B100")->{Value} returns an arrayref-of-arrayrefs (easy to iterate). See the Win32::OLE docs for examples. (Win32::OLE docs)
  • Use eq for string compares and == for numeric compares. Do not use = inside an if to test equality.
  • To avoid reading/writing Excel cell-by-cell in slow inner loops, load source rows into a hash first.

Example (minimal, safe pattern):

use strict;
use warnings;
use Win32::OLE qw(in);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;

my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application','Quit');
my $src = $Excel->Workbooks->Open('C:\path\extract_Products.xls');
my $dst = $Excel->Workbooks->Open('C:\path\ProductClonesTest.xls');
my $sws = $src->Worksheets(1);
my $dws = $dst->Worksheets(1);

my $src_last = $sws->Cells($sws->Rows->{Count},2)->End(xlUp)->{Row};
my $dst_last = $dws->Cells($dws->Rows->{Count},10)->End(xlUp)->{Row};

my %map;
for my $r (2 .. $src_last) {
  my $k = $sws->Cells($r,2)->{Value}; next unless defined $k && length $k;
  $map{$k} = [ $sws->Cells($r,4)->{Value}, $sws->Cells($r,5)->{Value} ];
}

for my $r (2 .. $dst_last) {
  my $k = $dws->Cells($r,10)->{Value}; my $v = $dws->Cells($r,11)->{Value};
  if (exists $map{$k} && defined $map{$k}[0] && $map{$k}[0] eq $v) {
    $dws->Cells($r,12)->{Value} = $map{$k}[1];
  }
}

$dst->Save;
$src->Close; $dst->Close;
undef $Excel;

A note on modules: is right that Excel::Writer::XLSX is powerful — but it only creates/writes files. For reading existing files without Excel installed, consider Spreadsheet::Read, Spreadsheet::ParseExcel (XLS) or Spreadsheet::ParseXLSX (XLSX). (Spreadsheet::Read · Spreadsheet::ParseExcel · Spreadsheet::ParseXLSX)

Troubleshoot: enable $Win32::OLE::Warn = 3, check for undefined cells before comparing, and always Close/undef COM objects to avoid Excel processes left running.

Recommended Answers

All 2 Replies

Member Avatar for Nole_diver

Trying to create a perl script to take in data from on Excel file($Obook), store it into an array. then compare portions of that array to columns in the second book($book).

and it would be compared to columns in the other excel file that will contain the same data. It will make sure the first value(array[0][n]) is equal to a value in the column, if it is, need to make sure the second value(array[1][n]) is the same and if both of those match. Then write out 3rd value(array[2][n]) to the last column on $book.

Some guidance would be great.
thanks for your help.

Member Avatar for wickedxter

I think it might be easier to use this module.. Excel::Writer::XLSX This has alot of functions already....

Be a part of the DaniWeb community

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