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

I want to add a variable length of text to a number of merged horizontal cells in a spreadsheet so that when more than one
line of text is required the row height is automatically increased so that all the text can be seen.
I have read in a book about writing Excel macros in VBA that the Range Object has a WrapText property that when true should do what I want.
I have attempted this in the Perl code below but all I can see is the first line and the row height has not changed. The Excel button shows that TextWrap is set for the cell when I move the cursor into the cell.
Can any Monk explain how I can achieve what I want?
use OLE;
use Win32::OLE::Const "Microsoft Excel";
use strict "vars";
my ($excel, $workbook, $sheet);   
#___ DEFINE EXCEL
$excel = CreateObject OLE "Excel.Application";
#___ MAKE EXCEL VISIBLE
$excel -> {Visible} = 1;
#___ ADD NEW WORKBOOK
$workbook = $excel    -> Workbooks -> Add;
$sheet    = $workbook -> Worksheets("Sheet1");
$sheet                -> Activate;
$sheet -> Range("B2:J2") -> Merge;
$sheet -> Range("B2:J2") -> {WrapText}   = "True";
$sheet -> Range("B2:J2") -> {Value} = "The cat sat on the mat under th
+e Christmas tree looking at the chocolate mouse that the little boy h
+ad dropped on the floor near the raoring log file in the ingle nook f
+ireplace";

[download]

Replies are listed 'Best First'.
Re: Problem seeing multiline text in Excel cells
by Gangabass (Vicar) on Jan 24, 2010 at 12:51 UTC
[reply]
      Thanks, it could well be but I do not think that is what it says 'on the tin (for WrapText).
      I added the following line and I can see all the text.
      $sheet -> Range("B2:J2") -> {RowHeight}   = 60;

      [download]
      However, the text is positioned at the bottom of the cell. So far my searches to find how to set the alignment
      so that it is at the top of the cell have failed. Can anyone help?
      Also is there any means of setting the height so that this is the correct value for the entered text so that there are no blank lines?
[reply]
[d/l]
        Using the reccomended method of recording a VBA macro, I found that the following two lines
        put the text to the top and left of the cell.
        $sheet -> Range("B2:J2") -> {HorizontalAlignment} = xlHAlignLeft;
$sheet -> Range("B2:J2") -> {VerticalAlignment} = xlTop

        [download]
        It still would be good to know how to make the height of the row to fit exaclty the height of however many lines of text there are.
[reply]
[d/l]

Back to Seekers of Perl Wisdom