broquaint

Read my Scratch Pad for a complete discussion on getting data from and manipulating data in an excel worksheet using Win32::OLE.

Honestly, if you're going to be opening the workbooks anyway, there is no reason to use two scripts to accomplish this task, just iterate through the Sheets object of each workbook starting from the 1 position. Otherwise use the SaveAs function of the Worksheet object

use strict;

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';


$Win32::OLE::Warn = 3; # Die on Errors.
#######################################
## ::Warn = 2 throws the errors, but ##
## expects that the programmer deals ##
#######################################

my $excelfile = 'c:\temp\win32\tabexample.xls';
my $excelout = 'c:\temp\win32\tabexample.tab';

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit'); 
   $Excel->{DisplayAlerts}=0;   

my $Book = $Excel->Workbooks->Open($excelfile);
   $Book->SaveAs({Filename =>$excelout,
                  FileFormat => xlCurrentPlatformText});
[download]

Update: I thought I should make a slight clarification about iterating through the Worksheets set based on sheet count. I was trying to say that the Worksheets object is 1 based, not 0 based. If, indeed, you wanted to skip the first work sheet, you would start with 2.

my $sheetcnt = $Book->Worksheets->Count();

foreach (2..$sheetcnt){
   print $Book->Worksheets($_)->{Name} ."\n";
   my $sheet = $Book->Worksheets($_);

   ##Do something with the sheet object##
}
[download]

Now, if you were going to fold the reading of the files into the Perl program, so that it could read either tab-delim or .xls files, then use Spreadsheet::ParseExcel.

------
We are the carpenters and bricklayers of the Information Age.

Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

I'm not knowledgable on vba, where could I find out more info on this? Would I be able to do this in the excel visual basic editor? Thanks

Yes, it's done in Excel's VB Editor. Read the helpfiles (i.e., hit F1). That's all I did.

------
We are the carpenters and bricklayers of the Information Age.

Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

--t. alex

"Here's the chocolates, and here's the flowers. Now how 'bout it, widder hen, will ya marry me?" --Foghorn Leghorn