At the risk of appearing even MORE embarrased that I already do, can anyone tell me what the following message means. I am trying to build 9 spreadsheets into a single xls book using Win32::OLE. I have gone so far as cloning the sample in the ActiveState doc & it works fine. The only difference I can see is that my broken code tries to loop through several files building one sheet per file. It appears that I can create the book, add 9 sheets, and set the column headings, but I can't change the sheet name, or populate the range of data cells.

Win32::OLE(0.1707) error 0x80020009: "Exception occurred" in PROPERTYPUT "Name" at CSV2xls.pl line 148
at C:/Perl/AS_Perl/lib/Win32/OLE/Lite.pm line 221
Win32::OLE::Tie::STORE('Win32::OLE::Tie=HASH(0x1f287e0)', 'Name', '') called at CSV2xls.pl line 148
Debugged program terminated. Use q to quit or R to restart

Here's the code piece 
# *************************************************
# 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 @rows;
  my @fields;
foreach $file (@csvFiles) {
  open (csvFILE, $file) || die "Cannot open $csvFile $!\n";
  $csvFile = $file;

  # Skip the first record (too long) of each file, we'll deal with it 
+later
  my $inBuf = <csvFILE>;                    
  $sheet_count++;

  while ($inBuf = <csvFILE>) {
    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);
    push @rows, [@fields]; 
  
  } # End of while inBuf, or we exceeded Safety Net

  close csvFILE;
# ****************************************
# Build the Spreadsheet from the CSV Array
#
  print "\nLoading Sheet \($sheet_count\) of $xlsFile from $csvFile \n
+";
  $Sheet = $Book->Worksheets($sheet_count);
  $Sheet->{Name} = substr($cvsFile,0,6); #This fails   

  # Insert column titles - hmmm wonder why THESE work?
  my $Range = $Sheet->Range("A1:J1");
  $Range->{Value} = [qw(Dept ItemCnt GrSales VoidCnt VoidAmt RefundCnt
+ RefundAmt
                        CouponCnt CouponAmt CustCnt)];
  $Range->Font->{Bold} = 1;   # This works too

# Add csv data to spreadsheet
  print "\n\nAdding data from $csvFile to Sheet $xlsFile\n";
  $Range = $Sheet->Range(sprintf "A2:J%d", 2+$#rows);
  $Range->{Value} = \@rows;   # This fails similar to Name
} # End of Foreach csvFile

# *********************************************
    
# Save workbook to file $xlsFile 
unlink $xlsFile if -f $xlsFile;      #This works fine
$Book->SaveAs("$directory\\$xlsFile");
$Book->Close;

[download]

Also, if I set a breakpoint & key the Name line by hand, then continue, it works fine. I'm at a loss. Thanks for any insight you can offer, this is the first time I've tried to use any :: module.

In reply to OLE module working 'part-time' by Aim9b

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.