Win32::OLE, Excel & Subtotals

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

Hello all, I've searched perlmonks and google, and countless dev forums on this without any luck. And it looks like a very simple problem also. Using Win32::OLE, I create an .xls, dump some data on it, and I need to run subtotals on the data. While I could always have the spreadsheet already created w/ a VBA macro on it, I'd rather do it on perl.

my $book1 = $Excel->Workbooks->Open("c:\\temp.xls");
my $sheet = $book1->Worksheets("yabadabadoo");
my $selection = $sheet->Range("A1:E100");

$selection->Subtotal->(GroupBy => 1, Function => xlCount, TotalList =>
+ Array(3), Replace => "True", PageBreaks => "True", SummaryBelowDate 
+=> "True");
[download]

I get the following on the *Subtotal* line: Undefined subroutine &main::Array called at <fileName>, <lineNumber> Any insight would be greatly appreciated. Thanks, -Facundo.

Comment on Win32::OLE, Excel & Subtotals Download Code

Replies are listed 'Best First'.
Re: Win32::OLE, Excel & Subtotals by vida0 (Initiate) on May 26, 2004 at 18:46 UTC
okay, thanks for those who tried. For anybody that has this problem in the future, the way (sorry, ONE OF THE WAYS) to do it is the following: `$selection->Subtotal(1,xlCount,3,1,1,1);` [download] Thanks again, -Facundo.	[reply] [d/l]
Re^2: Win32::OLE, Excel & Subtotals by Anonymous Monk on Mar 09, 2015 at 17:36 UTC
Just want to thank you for this post - helped me out a lot - spent a few hours and this code fixed it. Thanks!	[reply]
Re: Win32::OLE, Excel & Subtotals by dragonchild (Archbishop) on May 26, 2004 at 18:13 UTC
TotalList => Array(3), What is Array()? that's your problem - you're attempting to call a function in Perl that you haven't defined yet. ------ We are the carpenters and bricklayers of the Information Age. Then there are Damian modules.... sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon.* - flyingmoose I shouldn't have to say this, but any code, unless otherwise stated, is untested	[reply]
Re: Re: Win32::OLE, Excel & Subtotals by vida0 (Initiate) on May 26, 2004 at 18:24 UTC
part of the VBA code. Not indispensable thou. The following would accomplish the same thing: `$selection->Subtotal->(GroupBy => 1, Function => xlCount, TotalList => + 3, Replace => "True", PageBreaks => "True", SummaryBelowDate => "Tru +e");` [download] And now the error message is: Win32::OLE(0.1701) error 0x8002000f: "Parameter not optional" in METHOD/PROPERTYGET "Subtotal"	[reply] [d/l]
Re: Win32::OLE, Excel & Subtotals by Jenda (Abbot) on May 26, 2004 at 18:01 UTC
I don't think there should be the "->" between the "Subtotal" and the parameters: `$selection->Subtotal(GroupBy => 1, Function => xlCount, TotalList => A +rray(3), Replace => "True", PageBreaks => "True", SummaryBelowDate => + "True");` [download] Jenda Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Rick Osborne Edit by castaway: Closed small tag in signature	[reply] [d/l]
Re: Re: Win32::OLE, Excel & Subtotals by vida0 (Initiate) on May 26, 2004 at 18:15 UTC
nope, tried that. It has nothing to do with it. Thanks, thou.	[reply]