Can you tell me where I might find the various options for "FileFormat" documented? The documentation installed with the module on my Windows Active State Perl installation isn't very comprehensive.

...checks the current directory for any excel files and pulls them into an array.

I've had no success finding a way to make the filepaths in the sections dynamic.

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

my $ext = ".xls";
my $newext = ".csv";

my $Excel=Win32::OLE->new('Excel.Application');
$Excel->{Visible}=1;
$Excel->{DisplayAlerts}=1;    #Set to 0 when the code is working, but 
+keep at 1 while debugging.

my $dir = getcwd;
$dir =~ s/\//\\\\/g;        #Sort out the slashes and backslashes
opendir(DIR, $dir) or die "can't opendir $dir: $!"; #Cookbook recipe 9
+.5
while (defined(my $file = readdir(DIR))) {
     if ((!-d $file) and (substr($file, -length($ext)) eq $ext)) {    
+#Someone WILL call a directory something.xls. I have the scars.
        my $Book = $Excel->Workbooks->Open("$dir\\\\$file") or die "Ca
+n't open file $dir\\\\$file"; 
        my $sheet = $Book -> Worksheets(1);
        $sheet -> Range ('A:A') -> {NumberFormat} = "000";
            
            #The next line is pretty explicit to try to make it clear 
+what's going on.
        $Book->SaveAs({Filename     => $dir . "\\\\" . substr($file, 0
+, length($file) - length($ext)) . $newext, 
                       FileFormat   => 6, #xlCSV, 
                       CreateBackup => 0});
        $Book->Close;
    }
}
closedir(DIR);
$Excel->Quit;
[download]

In my many google searches, many people suggest creating VB Macros in Excel then converting them to comparable statements in Win32::Ole. However I've not had any luck finding any beginner's tutorials or samples of how to do that.

(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;
                }
[download]

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.
<><

generator