Win32::OLE Excel - Running Subtotal command

[dilipkvarma]

Hi All,

I am trying to create a subtotal in an Excel sheet through Perl script. Some how I am not able to make it work.

Here is what I wrote

$Sheet->Activate;
$Range = $Sheet->Range("A2:C$Row");
$Range->Select;
 
@TotalList = (2, 3);
$Excel->Selection->Subtotal({GroupBy => 1, Function => xlCount, TotalList => (2)});
 

Any help on this is highly appreciated

Thanks in advance
Dilip

[miller]

Whenever you post code, attempt to make it compilable by the expert reading it. In this instance, you have not stated what perl module you using (Where's your use Module statement?), and you've also not shown how you're creating the $Sheet variable. Yes, some of us might be familiar enough with parsing excel spreadsheets to guess. But don't make us guess.

- Miller

[dilipkvarma]

Hi Miller,

Thank you for pointing out my mistake, I accept that and my sincere apologies.

Please find below the compilable code:

use Win32::OLE;
 
#############################################
# Create Excel instance
#############################################
# Start Excel and make it visible
$Excel = Win32::OLE->new('Excel.Application');
$Excel->{SheetsInNewWorkbook} = 1;
$Excel->{Visible} = 1;
 
#############################################
# Add sheet
#############################################
my $Book = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets(1);
$Sheet->{Name} = 'Sample';
 
# Insert column titles
my $Range = $Sheet->Range("A1:C1");
$Range->{Value} = ["One","Two", "Three"];
$Range->Font->{Bold} = 1;
 
 
#############################################
# Generate some sample data
#############################################
for ($Row=2; $Row<6; $Row++) {
    $Range = $Sheet->Range("A$Row:C$Row");
    $Range->{1} = $Row;
    $Range->{2} = $Row;
    $Range->{3} = $Row;
}
 
for (; $Row<25; $Row++) {
    $Range = $Sheet->Range("A$Row:C$Row");
    $Range->{1} = $Row;
    $Range->{2} = $Row;
    $Range->{3} = $Row;
}
 
for ($R=2; $R<13; $R++) {
    $Range = $Sheet->Range("A$Row:C$Row");
    $Range->{1} = $R;
    $Range->{2} = $R;
    $Range->{3} = $R;
    $Row ++;
}
 
$Range = $Sheet->Range("A2:C$Row");
$Range->Sort($Range->Range("A2"));
 
$Sheet->Activate;
$Range = $Sheet->Range("A1:C$Row");
$Range->Select;
 
@TotalList = (2, 3);
$Excel->Selection->Subtotal({GroupBy => 1, Function => xlCount, TotalList => \@TotalList});
 
__END__
 

Thanks & regards
Dilip

[miller]

Unfortunately, you have surpassed my knowledge with regard to this specific knowledge. I very much prefer the other major package for creating excel files.

cpan Spreadsheet::WriteExcel

- Miller

[dilipkvarma]

Thank you for quick reply, but my site does not have that package installed and I do not have permissions to install it.

Dilip

[miller]

Well, here's a question for you. Normally, whenever I want to know about a module's functionality, I simply go straight to cpan:

cpan Win32::OLE

In this instance though, that is not all that illuminating. The problem is that in order to document the accepted functionality, it seems like they might have to document all the functionality of Excel itself. I can't imagine that is something that they would choose to do.

So my question to you is, where do you learn how to use this module? All the books about it are very much outdated. The Randal Schwartz book is back from 1997.

- Miller

[dilipkvarma]

It is an interesting question. In fact, i am relatively new to PERL and did not have any formal training on PERL. But I am reasonably good in Visual Basic and I used to do these using VBA. And my current assignment requires me to do the above mentioned manipulation using PERL. To get this done, I managed to find some sample PERL scripts that manipulates Excel data. And from there onwards, I was just trying to convert the VBA code into PERL. Since the above code did not throw any compilation or run-time errors, I concluded that the OLE module does support that function, but probably there is something wrong in the invocation.

Once again, thank you very much for your efforts towards this.

[miller]

Ahh. Well, if you have experience with VisualBasic, then you should be ahead of the curve in knowing how to use this functionality. Unfortunately, to be able to help you with the perl aspect of the problem, it takes more knowledge than I have of the VB side.

If you're looking for a quick introduction to perl, this resource is pretty good:

Beginning Perl

And as always, knowing perldoc is very important.

http://perldoc.perl.org/

- Miller

[dilipkvarma]

No worries Miller.

Thanks for the links (Beginning Perl) and thank you very much for your time and efforts.

Catch you later with some other PERL query

Dilip