Win32::OLE, Excel, textboxes

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

ActivePerl Build 631, libwin32 0.18

My real problem is that I have a need to put a large chunk of text into an Excel worksheet, and have it intelligently wrap to one page wide and autosize to fit vertically.

I figured I would do this with a textbox. Silly me, it won't make one. Can anyone either help me with my textbox troubles, or show me another way to format a cell gigantically full of text?

Here's some snippets:

use Win32::OLE qw(in with HRESULT);
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Const 'Microsoft Office 10.0 Object Library';
use Win32::OLE::Const 'OLE Automation';
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{DisplayAlerts}=0; 
my $Book = $Excel->Workbooks->Add();
$Book->SaveAs($excelfile);
my $Sheet = $Book->Worksheets("Sheet1");
$Sheet->Activate();       
$Sheet->{Name} = "DidItInPerl";
#stolen right from cacharbe!

my $TextBox = $Sheet->Shapes->AddTextBox('msoTextOrientationHorizontal
+', '10','10','0','0');
[download]

Gives me this error:

Win32::OLE(0.1502) error 0x80020005: "Type mismatch" in METHOD/PROPERTYGET "AddTextBox" argument 1

I tried playing with the use Win32::OLE Const '$crap', but I'm just guessing there.

Comment on Win32::OLE, Excel, textboxes Download Code

Replies are listed 'Best First'.
Re: Win32::OLE, Excel, textboxes by Jenda (Abbot) on Mar 18, 2003 at 18:45 UTC
Remove the quotes around the `msoTextOrientationHorizontal` Jenda P.S.: It seems to get defined by the `use Win32::OLE::Const 'Microsoft Office 10.0 Object Library';` [download]	[reply] [d/l] [select]
Re: Re: Win32::OLE, Excel, textboxes by finni (Beadle) on Mar 18, 2003 at 18:56 UTC
Diggity-damn. That did it. I think I was getting errors before I had done `use Win32::OLE::Const 'Microsoft Office 10.0 Object Library';` [download] , and had added the quotes, and then added that 'use' statement without also removing the quotes. The next thing for me to do is find out how to position that text box at a cell.	[reply] [d/l]
Re: Win32::OLE, Excel, textboxes by jsprat (Curate) on Mar 18, 2003 at 18:49 UTC
Just remove the single quotes from around msoTextOrientationHorizontal. It's a constant, at compile time the value is inlined, unless quoted. You may want to remove the quotes from the numeric arguments too ;) HTH	[reply]