Re^3: Win32::OLE Excel AutoFill not working

I think the problem is that Selection is not property of Excel, rather workbook or worksheet. I tried to elaborate a bit -- this works for me:

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

my $excel = Win32::OLE->CreateObject('Excel.Application') or die $!;
$excel->{Visible} = 1;

my $workbook  = $excel->Workbooks->Add;
my $worksheet = $workbook->ActiveSheet;

$worksheet->Range('A1:B2')->{Value} = [[1,2],[2,4]];

$worksheet->Range('A1:B2')->AutoFill({
    Destination => $worksheet->Range('A1:B17'),
    Type        => xlFillDefault,
});
[download]

I am creating new workbook in example above, but you can open one as you did and access individual worksheets with Sheets property:

my $ws2 = $workbook->Sheets('SheetName');
$ws2->...
[download]

Hope this helps

-- Roman

Comment on Re^3: Win32::OLE Excel AutoFill not working Select or Download Code

Replies are listed 'Best First'.
Re^4: Win32::OLE Excel AutoFill not working by DaveTheMan (Initiate) on Dec 24, 2009 at 14:03 UTC
I think Selection is a property of Excel, at least that's what the Object Browser says. Your code is working, and it is also showing what I believe was the real problem: Range() is a property of Excel and Worksheet, but I need to refer to the Range of the Worksheet when I want to use AutoFill. Thus, this code works, too: `$sh -> Range('A2:B2') -> Select; $excel -> Selection -> AutoFill({Destination => $sh->Range('A2:B2'), T +ype => xlFillDefault});` [download] Thank you very much for the help, Roman! Regards, Dave	[reply] [d/l]
Re^5: Win32::OLE Excel AutoFill not working by bobr (Monk) on Dec 24, 2009 at 20:03 UTC
Glad to hear that you moved forward. Apparently I was wrong in my assumption about Selection, however I think it must then refer to activecell in current workbook/worksheet. I generally try to avoid using Selection, usually just create couple of Range objects and call methods/write data using them. OLE interface has quite large call overhead, so minimizing number of operations can significantly speedup your app. -- Roman	[reply]
Re^4: Win32::OLE Excel AutoFill not working by Anonymous Monk on Dec 29, 2009 at 21:12 UTC
If I change your code to the following I get "Unable to get the AutoFill property of the Range class". `use Win32::OLE; use Win32::OLE::Const 'Microsoft Excel'; my $excel = Win32::OLE->CreateObject ('Excel.Application') or die $!; $excel->{Visible} = 1; my $workbook = $excel->Workbooks->Add; my $worksheet = $workbook->ActiveSheet; $worksheet->Range ('A1')->{Value} = 1; $worksheet->Range('A1')->AutoFill({ Destination => $worksheet->Range('A1:B17'), Type => xlFillDefault, });` [download] But when I change B17 to A17, it works as expected. Any idea why, given that you can select a single cell in Excel and auto-fill as many rows and columns as you like without failure.	[reply] [d/l]