(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

In reply to Re^6: Win32::OLE Excel search and replace commas by generator
in thread Win32::OLE Excel search and replace commas by generator

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.