Re^6: Win32::OLE Excel search and replace commas

[generator]

(John) Davies,

Thanks for the suggested sources. I have collected and recorded the FileFormat codes after converting them as you suggested. I was also pleased with the link you provided that finally allowed me to understand how a VB "Macro" within Excel could be converted to Win32::OLE directives. Clearly your googling yielded better results than mine.

The relative file path/name issue was a little challenging. (My plan is to compile this script with Active State's PDK and let the user put the program in any directory they choose along with the source excel files.) I found that the method use to indicate "currently logged directory" in the file grep command would not work similarly within the Win32::OLE statements.

I settled on using Win32::OLE's GetFullPathName call to pull the full path hoping it would work better than the ./ prefix used for discoverng the excel files.

The $Book SaveAs Filename gave me some headeaches because one of my source files had spaces in the name. I tried applying quote marks around the pathname/filename without success. Eventually I settled for stripping out the spaces in the name before writing the file back as a CSV.

I wasn't able to get your program to work, but am fairly satisfied with the current state of mine.

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

my $fildir = "./";
opendir DIR, $fildir;
my @files = grep { /.xls/ } readdir(DIR);
closedir DIR;
foreach my $files (@files)     {
    chomp $files;

my $pathfile = Win32::GetFullPathName($files);
my $adbspath = $pathfile;
$adbspath =~ s/\\/\\\\/g;

my $Excel=Win32::OLE->new('Excel.Application');
    $Excel->{Visible}=0;
    $Excel->{DisplayAlerts}=0;
    my $Book = $Excel->Workbooks->Open("$adbspath") or die "Can't open
+ file"; 
    my $sheet = $Book -> Worksheets(1);
    $sheet -> Range ('A:A') -> {NumberFormat} = "000";

my $nmlng = length $pathfile;
my $till = rindex ($pathfile,".xls",$nmlng);
my $nwnam = substr ($pathfile,0,$till);
# remove spaces from file name to facilitate saveas filename
$nwnam =~ s/ //g;
my $qnwnam = "\"".$nwnam.".csv\"";
print "$qnwnam \n";
$Book->SaveAs({Filename        => "$nwnam.csv", 
               FileFormat      => 6, #xlCSV, 
               CreateBackup    => 0});
#    unlink ($files);
$Excel->Quit;
                }

Again thanks for the answers. I'm feeling a whole lot better about the future opportunities to convert excel spreadsheets to usable data import files.