How to resize a column in excel with Win32::OLE

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

Hello Perl Monks,

I am unable to change the width of a column of microsoft excel with perl.
Below is my code, please tell me how can I resize my column. The code below gives me error

Win32::OLE(0.1502) error 0x80020009: "Exception occurred" in PROPERTYPUT "ColumnWidth" at ole.pl line 10 quitting Microsoft Excel

use strict;
use warnings;
use Win32::OLE;

my $excel = new Win32::OLE("Excel.Application", \&EndApp);
$excel->{Visible} = 1;
my $workbook = Win32::OLE->GetObject("c:\\test.xls");
my $worksheet = $workbook->Worksheets(1);
my $column = $worksheet->Range("A1");
$column->{ColumnWidth} = 400;

sub EndApp
{
  my $comobject = $_[0];
  print "quitting ". $comobject->{Name}."\n";
  $comobject->Quit();
}
[download]

regards,
Abhishek.

Comment on How to resize a column in excel with Win32::OLE Download Code

Replies are listed 'Best First'.
Re: How to resize a column in excel with Win32::OLE by jmcnamara (Monsignor) on Feb 06, 2003 at 13:40 UTC
This line doesn't look right: `my $column = $worksheet->Range("A1");` [download] It should probably be: `my $column = $worksheet->Columns("A:A");` [download] See cacharbe's excellent tutorial, Using Win32::OLE and Excel - Tips and Tricks. It contains an example of how to change column widths. -- John. Update: Corrected "A" to "A:A", see below.	[reply] [d/l] [select]
Re: Re: How to resize a column in excel with Win32::OLE by abhishes (Friar) on Feb 06, 2003 at 13:52 UTC
Thanks for your response. Your suggestion seems to be correct. since I am selecting the whole column, A1 seems wrong. I changed the code to $worksheet->Columns("A"); but it crashed again. This time the error message was Win32::OLE(0.1502) error 0x80020009: "Exception occurred" in PROPERTYPUT "ColumnWidth" at ole.pl line 10 quitting Microsoft Excel regards, Abhishek.	[reply]
Re: Re: Re: How to resize a column in excel with Win32::OLE by jmcnamara (Monsignor) on Feb 06, 2003 at 14:11 UTC
Sorry, that should have been `$worksheet->Columns("A:A")`. Here is a working example: `#!/usr/bin/perl -w use strict; use Cwd; use Win32::OLE; my $application = Win32::OLE->new("Excel.Application"); my $workbook = $application->Workbooks->Add; my $worksheet = $workbook->Worksheets(1); $worksheet->Cells(1,1)->{Value} = "Hello World"; $worksheet->Columns("A:A")->{ColumnWidth} = 25; # Get current directory using Cwd.pm my $dir = cwd(); $workbook->SaveAs($dir . '/win32ole.xls'); $workbook->Close;` [download] -- John.	[reply] [d/l]