Win32::OLE: how to shif cell contents left in excel

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

salutations,

i need to massage an excel datasheet i got from a client. for reproducibility and accountability, i am using perl's Win32::OLE module. i would like to do simple things, like select a range and overwrite it with the content that lies to the left.

the following code highlights the range but croaks with

$ perl -w xlcolsel.pl

Win32::OLE(0.1603) error 0x80020003: "Member not found" in METHOD/PROPERTYGET "" at xlcolsel.pl line 21

Can't call method "Delete" on an undefined value at xlcolsel.pl line 21.

use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';

my $filename = "c:\\clients\\xxx\\test.xls";

my $xl = Win32::OLE->GetActiveObject('Excel.Application') 
    || Win32::OLE->new('Excel.Application', 'Quit');
$xl->{Visible} = 1;
my $book = $xl->Workbooks->Open( $filename );
my $sheet = $book->Worksheets("Sheet1");
$sheet->Range('a1:c35')->Select;

$sheet->Selection->Delete({ Shift=> xlLeft});   # line 21
[download]

the error message tells me i should somehow assign the highlighted range to an object other than sheet, but i'm at a loss as to how to do so.

i would appreciate any help.

Comment on Win32::OLE: how to shif cell contents left in excel Download Code

Replies are listed 'Best First'.
Re: Win32::OLE: how to shif cell contents left in excel by jsprat (Curate) on Oct 06, 2003 at 20:52 UTC
"Selection" is a property of the Application object, not the Sheet object. The application object is $xl in your code. You will probably want to have a look at the Object Model in the Excel help file. Line 21 should look something like this: `$xl->Selection->Delete({ Shift => xlToLeft });` Also, note that the constant is xlToLeft, at least with Excel 97.	[reply] [d/l]


more useful options
	PerlMonks