By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,279 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Win32::OLE Excel - Running Subtotal command

P: 8
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
Share this Question
Share on Google+
8 Replies


miller
Expert 100+
P: 1,089
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

P: 8
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
Expert 100+
P: 1,089
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

P: 8
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
Expert 100+
P: 1,089
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

P: 8
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
Expert 100+
P: 1,089
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

P: 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.