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

Hi,

I want to "copy" a a few cells onto another range and let Excel decide how to do that (i.e. Autofill with xlFillDefault). It is not working in perl. Here's the macro which I've recorded in Excel: 

Range("A2:B2").Select
Selection.AutoFill Destination:=Range("A2:B17"), Type:=xlFillDefault

[download]

And my perl code: (I've copied the relevant syntax from http://code.mincus.com/42/perl-win32ole-excel-selection-texttocolumns/ and think it should be working) 

my $excel = Win32::OLE::Strict->new('Excel.Application', 'Quit');
my $workbook = $excel    -> Workbooks -> Open("$templfile");
my $sh    = $wb -> Worksheets(1) -> {Name};
$sh -> Activate;

my $srcrange = 'A2:B2';
my $targrange = 'Range("A2:B17")';


$sh -> Range("$srcrange ") -> Select;
my $xlfill = 'xlFillDefault';
$excel -> Selection -> AutoFill({Destination => $targrange, Type => $x
+lfill});

[download]

What happens is that the "from-range" (A2:B2) gets selected. The AutoFill seems to be ignored (no error message, no action). Any ideas?

Thanks,
Dave

Replies are listed 'Best First'.
Re: Win32::OLE Excel AutoFill not working
by bobr (Monk) on Dec 23, 2009 at 18:50 UTC
    The constant xlFillDefault should not be string. Try using this: 
    use Win32::OLE::Const 'Microsoft Excel';

...

$excel -> Selection -> AutoFill({Destination => $targrange, Type => xl
+FillDefault});

    [download]
    Constants are numeric, in fact xlFillDefault equals to zero.

    Hope this helps.

    -- regards, Roman

[reply]
[d/l]
      Thank you very much Roman!

      Now I am getting the following error:

      OLE exception from "Microsoft Office Excel":

Unable to get the AutoFill property of the Range class

Win32::OLE(0.1707) error 0x800a03ec
    in METHOD/PROPERTYGET "AutoFill"

      [download]

      Haven't been able to sort this out yet

      Regards,
      Dave
[reply]
[d/l]
        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

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom