Problem adding long string to excel cell

Anchor has asked for the wisdom of the Perl Monks concerning the following question:

Hello, I am hoping someone can help me out. I searched the web for an answer but cannot find one.
My program does some processing and creates an excel log file. Sometimes it has to put data (a string) in a cell that is 1000 characters long. But when the code tries to put the long string into the cell, I get the following error:

Win32::OLE(0.1403) error 0x80020009: "Exception occurred" in PROPERTYPUT "Value" at ProcessData.pl line 709

And when I check the excel doc after the processing is done the really long strings that caused the exceptions where not inserted into cells that they were suppose to get inserted into. I'm not doing anything special but I am thinking that I might have to. The problem is only when Excel 2003 is installed. If I run the same code on a machine with Excel 2007 installed everything works fine. Here is the code that puts the string into the excel file.

  $worksheet = $workbook->Worksheets(3);
  $worksheet->{Name} = "Invalid Messages";
  $worksheet->Range("A:A")->{HorizontalAlignment} = xlHAlignLeft;
  $worksheet->Range("A:A")->{ColumnWidth} = 10;
  $worksheet->Range("B:B")->{HorizontalAlignment} = xlHAlignLeft;
  $worksheet->Range("B:B")->{ColumnWidth} = 200;

  # Process all of the 'bad' messages.
  my $sTextData;
  foreach $sMsg (@aBadMessages)
  {
        @Params = split('\|', $sMsg);
        $sTextData = @Params[1];
        $range = $worksheet->Range(sprintf("%s%d:%s%d", 'A', $nRow, 'C
+', $nRow));
        $range->{Value} = [@Params[0], $sTextData, @Params[$#Params]];
    }

    $workbook->SaveAs($sLogFileName);
    $ExcelLog->Quit(); # leave excel
[download]

I know a 1000 character length string can go into a cell through the UI but its not working through my perl code. Is there something I need to do before putting the 1000 character string into the cell?

Any help would be greatly appreciated. Thanks.

Comment on Problem adding long string to excel cell Download Code

Replies are listed 'Best First'.
Re: Problem adding long string to excel cell by Anchor (Novice) on Apr 16, 2010 at 14:30 UTC
Okay. I found a solution while messing around with the code. Instead of doing the following code to set the values of a range of cells at once like this: `$range = $worksheet->Range(sprintf("%s%d:%s%d", 'A', $nRow, 'C', $nR +ow)); $range->{Value} = [@Params[0], $sTextData, @Params[2];` [download] I changed it so the code added the values to the cells one at a time like this: `$range = $worksheet->Range(sprintf("%s%d", 'A', $nRow)); $range->{Value} = @Params[0]; $range = $worksheet->Range(sprintf("%s%d", 'B', $nRow)); $range->{Value} = $sTextData; $range = $worksheet->Range(sprintf("%s%d", 'C', $nRow)); $range->{Value} = @Params[2];` [download] For some reason, this did the trick. I haven't gotten the exception anymore and the long text is now showing in the cell where as before it wouldn't get inserted. If anyone has a good explanation as to why this code makes that much of a difference I would be more than happy to listen.	[reply] [d/l] [select]
Re^2: Problem adding long string to excel cell by googleabc (Initiate) on Aug 03, 2010 at 07:43 UTC
Thanks very much for your solution.	[reply]
Re^2: Problem adding long string to excel cell by dasgar (Priest) on Aug 03, 2010 at 16:21 UTC
I don't claim to be an expert, but here's my guess. Your first method that failed looks like what it's doing is the equivalent of highlighting and selecting 3 cells and then trying to put values in there. The `$range->{Value}` is expecting a scalar (such as a number or string). So when you try to give it an array, you're giving it something that it's not expecting. In your second method that is working, your giving `$range->{Value}` scalars.	[reply] [d/l] [select]