below is a script that i need yr help on
i got this to print out a csv to excel
i'm trying to modify it to read a .txt file to convert an excel
hm...doesn't seem to work still any idea why it can't run?
is it cos i should not be open my file in the win32::OLE module?
Expand|Select|Wrap|Line Numbers
- **************************************************************
- # Start Excel and create new workbook with 9 sheets
- use Win32::OLE qw(in valof with);
- use Win32::OLE::Const 'Microsoft Excel';
- use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG);
- my $lgid = MAKELANGID(LANG_ENGLISH, SUBLANG_DEFAULT);
- $Win32::OLE::LCID = MAKELCID($lgid);
- $Win32::OLE::Warn = 3;
- my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
- $Excel->{SheetsInNewWorkbook} = 9;
- my $Book = $Excel->Workbooks->Add;
- my $Sheet = $Book->Worksheets(1);
- my $sheet_count = 0;
- # **************************************************************
- # Read each 'selected' file and load them into an array
- my $txtName;
- my $xlsRange;
- foreach $file (@txtFiles) {
- open (txtFILE, $file) || die "Cannot open $file $!\n";
- my @rows;
- $txtFile = $file;
- $#rows = -1;
- $#fields = -1;
- # Skip the first record (too long) of each file, we'll deal with it later
- my $inBuf;
- $sheet_count++;
- while ($inBuf = <txtFILE>) {
- my @fields;
- chomp($inBuf);
- if (scalar @fields > $line_limit) {last;} # Runaway safety net
- $inBuf =~ s/^\"//; # Take out any LEADING or
- $inBuf =~ s/\"$//; # TRAILING double quotes
- # OK, Process this record
- @fields = split(/\,/,$inBuf);
- # print "\nProcessing record ".scalar(@rows)."\n @fields";
- push @rows, [@fields];
- } # End of while inBuf, or we exceeded our Runaway Safety Net
- close csvFILE;
- # *******************************************************************
- # Build the Spreadsheet from the CSV Array
- #
- print "\nLoading Sheet \($sheet_count\) of $xlsFile from $txtFile \n";
- $Sheet = $Book->Worksheets($sheet_count);
- $csvName = substr($txtFile,0,6);
- $Sheet->{Name} = "$txtName";
- # Add csv data to spreadsheet
- print "\n\nAdding data from $csvFile to Sheet $xlsFile\\$csvName\n";
- print "\tUsing a range of A4:J50 \n";
- $xlsRange = sprintf("A1:J%d", 2+$#rows);
- $Range = $Sheet->Range("$xlsRange");
- $Range->{Value} = \@rows;
- } # End of Foreach csvFile
- # *******************************************************************
- # Save workbook to file $xlsFile
- unlink $xlsFile if -f $xlsFile;
- $Book->SaveAs("$directory\\$xlsFile");
- $Book->Close;
- print "End of import.\n";