469,360 Members | 1,796 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,360 developers. It's quick & easy.

Win32::OLE Excel - Running Subtotal command

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

Expand|Select|Wrap|Line Numbers
  1. $Sheet->Activate;
  2. $Range = $Sheet->Range("A2:C$Row");
  3. $Range->Select;
  4.  
  5. @TotalList = (2, 3);
  6. $Excel->Selection->Subtotal({GroupBy => 1, Function => xlCount, TotalList => (2)});
  7.  
Any help on this is highly appreciated

Thanks in advance
Dilip
Jul 17 '07 #1
8 4858
miller
1,089 Expert 1GB
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
Jul 17 '07 #2
Hi Miller,

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

Please find below the compilable code:

Expand|Select|Wrap|Line Numbers
  1. use Win32::OLE;
  2.  
  3. #############################################
  4. # Create Excel instance
  5. #############################################
  6. # Start Excel and make it visible
  7. $Excel = Win32::OLE->new('Excel.Application');
  8. $Excel->{SheetsInNewWorkbook} = 1;
  9. $Excel->{Visible} = 1;
  10.  
  11. #############################################
  12. # Add sheet
  13. #############################################
  14. my $Book = $Excel->Workbooks->Add;
  15. my $Sheet = $Book->Worksheets(1);
  16. $Sheet->{Name} = 'Sample';
  17.  
  18. # Insert column titles
  19. my $Range = $Sheet->Range("A1:C1");
  20. $Range->{Value} = ["One","Two", "Three"];
  21. $Range->Font->{Bold} = 1;
  22.  
  23.  
  24. #############################################
  25. # Generate some sample data
  26. #############################################
  27. for ($Row=2; $Row<6; $Row++) {
  28.     $Range = $Sheet->Range("A$Row:C$Row");
  29.     $Range->{1} = $Row;
  30.     $Range->{2} = $Row;
  31.     $Range->{3} = $Row;
  32. }
  33.  
  34. for (; $Row<25; $Row++) {
  35.     $Range = $Sheet->Range("A$Row:C$Row");
  36.     $Range->{1} = $Row;
  37.     $Range->{2} = $Row;
  38.     $Range->{3} = $Row;
  39. }
  40.  
  41. for ($R=2; $R<13; $R++) {
  42.     $Range = $Sheet->Range("A$Row:C$Row");
  43.     $Range->{1} = $R;
  44.     $Range->{2} = $R;
  45.     $Range->{3} = $R;
  46.     $Row ++;
  47. }
  48.  
  49. $Range = $Sheet->Range("A2:C$Row");
  50. $Range->Sort($Range->Range("A2"));
  51.  
  52. $Sheet->Activate;
  53. $Range = $Sheet->Range("A1:C$Row");
  54. $Range->Select;
  55.  
  56. @TotalList = (2, 3);
  57. $Excel->Selection->Subtotal({GroupBy => 1, Function => xlCount, TotalList => \@TotalList});
  58.  
  59. __END__
  60.  
Thanks & regards
Dilip
Jul 18 '07 #3
miller
1,089 Expert 1GB
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
Jul 18 '07 #4
Thank you for quick reply, but my site does not have that package installed and I do not have permissions to install it.

Dilip
Jul 18 '07 #5
miller
1,089 Expert 1GB
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
Jul 18 '07 #6
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.
Jul 19 '07 #7
miller
1,089 Expert 1GB
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
Jul 19 '07 #8
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
Jul 20 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by M. David Allen | last post: by
2 posts views Thread by usenet | last post: by
1 post views Thread by user1357 | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.