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

I am using Win32::OLE::Const ‘Microsoft Excel’ to successfully read and write spreadsheets.
However, I believe that I am using only a small subset of the possible functions.
Does anyone know of sources (web sites books and so on) that give
* a summary of all the functions that can be used
* examples of how to use them.

I find that I can only use these features if Excel is also loaded on to the PC.
Is this absolutely necessary? If not what has to be done?

Many thanks in advance for any clues and help.

Replies are listed 'Best First'.
Re: Reading & Writing Excel Spreadsheets
by Corion (Patriarch) on May 04, 2005 at 07:52 UTC

    Win32::OLE is merely a gateway, a door, to Microsoft Excel. This is why you need Microsoft Excel installed on the machine where you want to use it. If you don't want to use it, consider SpreadSheet::WriteExcel.

    As Microsoft is the manufacturer of Excel, they also have vast documentation and example code for Excel on the MSDN. It is mostly written in VBA, but translation of VBA to Perl is not really hard.

Re: Reading & Writing Excel Spreadsheets
by displeaser (Hermit) on May 04, 2005 at 09:26 UTC
    Hi,
    If you use the activestate version of Perl, it comes with an object browser, using this you can see what is available.
    For example, on selecting "Microsoft Excel 9.0 Object Library" in the main window, there are 2 lists populated with ole objects and functions available to the object.
    As Corion said MSDN is the best place for info. The later versions of the Activestate Perl dev kit come with a vbscript convertor, which may be useful when converting examples from msdn.
    Hope this helps.
Re: Reading & Writing Excel Spreadsheets
by tlm (Prior) on May 04, 2005 at 09:40 UTC

      Ditto tlm's remarks. I've used both ::ParseExcel and ::WriteExcel with much sucess.

      The only thing awkward I've found is creating and formating date/times. Alas, this problem rears it's ugly head all the time (PHP,MySQL,etc)

Re: Reading & Writing Excel Spreadsheets
by gellyfish (Monsignor) on May 04, 2005 at 09:26 UTC

    The Microsoft documentation for the Excel VBA is here.

    /J\