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.This is one of my pet peeves. Thank you for stroking it. Yes, the documentation is not what it should be. The VBA constants are documented here. My nose tells me that there's some way of importing them to Perl as named constants, but I haven't looked for or stumbled on it as yet. However, converting them to their actual values is trivial - start Excel, bring up the IDE (Alt-F11), go to the Immediate pane (Ctrl-G) and type in ?xlcsv or whichever you want (VBA doesn't care about case). It will return the value.
...checks the current directory for any excel files and pulls them into an array.I'm not ecstatic about this approach. Without suggesting that it won't work, I would tend to loop through the directory, checking each file in turn. The problem I have with your approach is that if the directory is shared, you are either blocking access to all files or risking a file being deleted or added between the start and end of the process. By handling files one at a time, the period in which this can happen is minimised.
I've had no success finding a way to make the filepaths in the sections dynamic.I think this is down to the slashes and backslashes again.
I haven't tested it rigorously - I don't want to make dozens of files I don't need, especially as I do use CSVs - but I have tested most parts of it, and I think it will do what you need. If I have read the substr docs correctly, it can be used to change the extension directly, but that's beyond (a) me, (b) the scope of this lecture. :-)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;
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.I'm not sure whether you mean writing VBA or converting it. As far as converting is concerned, this is the classic reference. For writing VBA, Googling "excel vba introduction" gives lots of places. I'm a Perl beginner but not an Excel beginner, so I can't really recommend any of them. "Excel $year Power Programming With VBA" by John Walkenbach is outstanding. It starts with baby steps and eventually gets seriously sophisticated.
In reply to Re^5: Win32::OLE Excel search and replace commas
by davies
in thread Win32::OLE Excel search and replace commas
by generator
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |