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

Hi all,

I would like to learn how work with Excel files from Perl, extracting and processing data. Any suggestions for good references on the object model? The POD for Win32::OLE says to look at the "Visual Basic Reference Guide", any ideas where I could find that, or some other (hopefully online) reference?

Replies are listed 'Best First'.
Re: Perl-Excel Guidelines
by jsprat (Curate) on Jul 16, 2003 at 17:16 UTC
    Here at perlmonks: MSDN:
    • Excel object model (you may need to poke around for your specific version of excel, but overall it hasn't evolved much that I've seen)
    On your local hard drive:
    • OLE-Browser - installed with perl, look in the ActiveState menu (if you're running ActiveState perl)
    • Open Excel, hit Alt-F11 to open VBA environment, then hit F2 to view the Object Browser
    • The Excel VB help file has the object model in detail. This helpfile is not installed by default, so you may need to rerun setup.
    You'll also need curly brackets around properties, and method calls look the same as they do in straight perl:

    VBPerl
    Properties:Selection.Hidden = True $xls->Selection->{Hidden} = 1;
    Methods:Worksheets(1).Activate $xls->Worksheets(1)->Activate;

    A couple of things about Win32::OLE you may find helpful: 

    • Win32::OLE->Option( Warn => 3 ); 
# or 2 or 4

      [download]
    • # load all excel constants (I may have the library name wrong)
use Win32::OLE::Const 'Microsoft Excel';

      [download]
    • # make it a little easier to iterate over collections
use Win32::OLE qw/IN WITH/;

      [download]

    Update: Closed the list tag, added missing tr.

[reply]
[d/l]
[select]
Re: Perl-Excel Guidelines
by dreadpiratepeter (Priest) on Jul 16, 2003 at 16:27 UTC
[reply]
Re: Perl-Excel Guidelines
by Aristotle (Chancellor) on Jul 16, 2003 at 16:31 UTC

    You'll have to look for whether Microsoft has documented anything if you're going to use Win32::OLE. The module is a generic OLE interface; documentation for a specific applications API belongs into that specific application's documentation, not with Win32::OLE.

    But you might be interested in Spreadsheet::ParseExcel and Spreadsheet::WriteExcel.

    Makeshifts last the longest.

[reply]
      Open Excel.. choose tools..macros... visual basic editor..then click the object browser icon in the toolbar... its a box with 3 things coming out of it.
      Then you see the object browser. On the left you see the classes. On the right you see the methods. to receive help on a selected method you have to hit the question mark on the object browser window with the method or class selected.
      just a note.. with ole you reference methods and properties like so
      my $excel  = Win32::OLE->new("Excel.Application");
$excel->{someproperty}
$excel->somemethod();

      [download]
      For collections of objects such as worksheets, you can either use 
      $excel->collection($someindex) ;
||
$excel->collection->Item($someindex);

      [download]
      to reference specific items in the collection. Hope this helps.
[reply]
[d/l]
[select]
Re: Perl-Excel Guidelines
by Anonymous Monk on Jul 16, 2003 at 17:47 UTC
    Thank you fellow monks. I have it working like a MS-Powered Charm now. Hmm... actually, that isn't very charm-like, now that I think about it. Thanks again. :)
[reply]

Back to Seekers of Perl Wisdom