In addition to this very good introduction, here's my approach to automating things in Excel that I know how to do manually in Excel. These steps apply to any (Office) application that has a macro recorder:
- Start the Excel Macro Recorder
- Perform the desired action(s) manually, for example, delete a row or bolden text or create a pivot chart
- Stop the Excel Macro Recorder
- Inspect the generated VB code
- Check that the generated VB code does what you want
- Translate the generated VB code to Perl / Win32::OLE invocations
That's all there is to it and it sure beats poring over the documentation to find out the needed function names.
Similar lists exist at Re^3: Problems with Microsft's new Office 'XML' and Re: Convert word(.doc) file to html file, but as this appears to be a good starting page for people working with Win32::OLE, I figured to put it here as well.