Thanks again for sticking with me on this. You are right about my taking the "long way" around to get my CSV file. As I stated in my original post I was trying to replicate the steps I took manually to accomplsh the same thing. Until your post, I could not find how to replicate the search and replace finding commas and replacing with nothing

After playing with your suggestions (and your code) I noted that your use of the "FileFormat" in... 

$Book->SaveAs({Filename        => "F:\\Assess\\Assessment.csv", 
               FileFormat      => 6, #xlCSV, 
               CreateBackup    => 0});

[download]
...cleaned up any (text) fields with commas in them -- my biggest problem.

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. The best piece of guidance I've found on this module was online at http://www.tek-tips.com/faqs.cfm?fid=6715.

After finding your second response (almost a month after you posted it), I revisited this project and extended it a little to correct for situations where users enter a 1 or 2 digit code where my accounting program will expect to find a three digit code with leading zeros. By formatting the range containing the codes before saving the file as CSV I can correct for that.

My current version (still a work in progress), here... 

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 $Excel=Win32::OLE->new('Excel.Application');
    $Excel->{Visible}=0;
    $Excel->{DisplayAlerts}=0;
    my $Book = $Excel->Workbooks->Open("C:\\Monks\\$files") or die "Ca
+n't open file"; 
    my $sheet = $Book -> Worksheets(1);
    $sheet -> Range ('A:A') -> {NumberFormat} = "000";

my $nmlng = length $files;
my $nwnam = substr ($files,0,($nmlng - 4));

$Book->SaveAs({Filename        => "C:\\Monks\\$nwnam.csv", 
               FileFormat      => 6, #xlCSV, 
               CreateBackup    => 0});

    unlink ($files);
$Excel->Quit;
                }

[download]
...checks the current directory for any excel files and pulls them into an array. Then each entry in that array is pushed through the routine you provided and the export file is like named with the CSV extant.

Typical excel data would look like:

Code,PropertyName,Unit Count,Unit Cost, Total Cost, TypeAbbreviation, 
+Comments
1,First Account,5,10.00,50.00,cod,User text here
002,"Second Account, The",10,20.00,200.00,cod,More narrative
003,Third Account,5,20.00,100.00,cod,Another comment

[download]

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

my $Book = $Excel->Workbooks->Open("C:\\Monks\\$files") or die "Can't 
+open file";

[download]
$Book->SaveAs({Filename        => "C:\\Monks\\$nwnam.csv",

[download]
...sections dynamic. I tried "$files", "./$files" and "$fildir/$nwnam.csv" without success. Any ideas? It would be great if I could use the same code no matter where the user chooses to put the files as long at the compiled perl program is in the same directory with the excel workbooks.

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. If you can suggest a source or process, I'd appreciate it.

Thanks again. As a interested (but novice) Perl programmer, people like you and most of the other Monks who take the time to point the way are what keeps me from quitting in frustration.

<><generator

In reply to Re^4: 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.