I'm not sure what your question is. This is not a code writing service. See Using Win32::OLE and Excel - Tips and Tricks. Alternatively, if you need your code to work without Excel installed, also see Spreadsheet::ParseExcel, which also comes with xls2csv.pl.

Thanks for the feedback. I didn't find a way to run a search and replace using OLE. (I was attempting to replicate the manual steps I took, while manually editing these files).

I did, however, find a solution. I extracted the cell values then ran a substitute =~ s/,//; on the scalar value before writing it to a text file, followed by a comma.

#!c:/perl/bin/perl.exe

use strict;
use warnings;
use Win32::OLE qw(in with);

use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;


open (OUTPUT,">>","c:/assess/output.txt");
 
my $Excel = Win32::OLE->GetActiveObject
    ('Excel.Application')
        || 
Win32::OLE->new('Excel.Application', 'Quit'); 

my $Book = $Excel->Workbooks->Open
("C:/assess/assessment.xls"); 

my $Sheet = $Book->Worksheets(1); 

my $LastRow = $Sheet->UsedRange->Find({What=>"*",
     SearchDirection=>xlPrevious,
     SearchOrder=>xlByRows})->{Row};

my $array = $Sheet->Range("B2:"."F".$LastRow)->{'Value'}; 
foreach my $ref_array (@$array) { 
foreach my $scalar (@$ref_array) {
$scalar =~ s/,//;
print OUTPUT "$scalar,";
}
print OUTPUT "\n";
}
$Book->Close;
[download]

Your warning regarding the use of commas in formulas was wise but in this case irrelevant. The subsequent processing used only integer values associated with unit cost, unit rate and account code (text string). All fields with formulas are ignored.

Thanks again for taking the time to reply. It ain't pretty but it will do the job.

<><

generator

Ah, the joys of having code to look at. You're going to a lot of trouble to generate the CSV, when Excel will do it for you automatically.

use strict;
use warnings;
use Win32::OLE;

my $Excel=Win32::OLE->new('Excel.Application');
$Excel->{Visible}=1;
$Excel->{DisplayAlerts}=0;
my $Book = $Excel->Workbooks->Open("F:\\assess\\assessment.xls") or di
+e "Can't open file"; 
$Book->SaveAs({Filename        => "F:\\Assess\\Assessment.csv", 
               FileFormat      => 6, #xlCSV, 
               CreateBackup    => 0});
$Excel->Quit;
[download]

A few points. First, indenting code within if blocks, loops etc. will make it easier for you to understand what your code is doing in a few months' time. Second, I commented in Re^3: Win32::Ole excel external data range on both with and relying on an existing instance of Excel. I suspect both you and the other OP copied code from the same place. Third, the thread I mentioned earlier describes dealing with embedded commas and quotes, but the general rule for CSVs is that you can embed a comma in quotes. The output of my test file is:

1,2,3,4
2,"Who, What?",4,5
3,4,5,6
4,5,6,7
[download]

Update:
If, despite this, you really need to get rid of commas, the following code will remove commas from text:

use strict;
use warnings;
use Win32::OLE;

my $Excel=Win32::OLE->new('Excel.Application');
$Excel->{Visible}=1;
my $Book = $Excel->Workbooks->Open("F:\\assess\\Lorem.xls") or die "Ca
+n't open file"; 
$Excel->Cells->Replace({
    What          => ",", 
    Replacement   => ""});
[download]

This won't get rid of formatting commas in numbers. To do that, you will need to change the formats. But from your code, you're looking at the underlying numbers anyway, rather than printing a file.
End of update

Regards,

John Davies