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

Most worthy Monks-

I have a perl script, reporter.pl, which opens excel spreadsheets, issues the RefreshAll command to pull the latest data from a db2 database into the spreadsheet, and then mails the resulting updated spreadsheet to the specified user. The script is written in a way that allows any of my coworkers to add a new file to the list of updated files by creating the appropriate directory structure and updating an ini file. This is all done on a windows 2003 server, or XP when testing on my laptop.

A recent feature addition was to allow the user, in the ini file, to specify macros that should be run before and/or after the RefreshAll command. This has introduced an unanticipated (though perfectly obvious) problem: How do I, in my perl script, capture and react to errors caused in the excel macro? Fixing the macros is not always an immediate option and I'm looking for a way to gracefully close down excel and move on to the next report without requiring manual intervention.

I am using Win32::OLE to interface into excel and am able to capture OLE errors without any problem. This does have the unfortunate side affect of preventing me from using Threads or fork, since Win32::OLE is not threadsafe.

I've searched around a bit and everything seems to be telling me how to capture/handle excel and/or ole errors, but not errors that occur within an excel macro. Any help is much appreciated.

Replies are listed 'Best First'.
Re: Capturing excel macro errors
by roboticus (Chancellor) on Dec 01, 2007 at 03:40 UTC
    jrsimmon:

    Perhaps you should make your macros execute not when they're opened but on some user action so you can be sure that there will be someone there to fix/report the problem?

    ...roboticus

[reply]
      The purpose of the reporter script is to automate a group of reports that my team uses daily to monitor the status of all the machines we support. We're trying to get away from any user intervention at all.

      I am gradually building in ways to detect that a report is hung (the typical end result of a macro error), but it would be so much better if I could just get the error passed back to OLE instead of having to figure out that it has occurred by polling the appl, checking runtime, etc...
[reply]
        Perhaps you might have success with Spreadsheet::WriteExcel? I've had very good luck with that. (Originally had to use it because we can't install Office on our servers.)

        It might not be able to do everything you can do via OLE automation, but it doesn't tend to have error messages with dialog boxes, either.

        ...roboticus

[reply]

Back to Seekers of Perl Wisdom