Re: Win32::OLE: how to call Excel VBA macros and catch all VBA errors without dialog boxes appearing?

[BrowserUk]

It is possible to control whether system hard error dialogs are displayed (or not), on a per-process basis, using the system API SetErrorMode(). But in your case, this would need to be applied to Excel.exe.

The error mode setting is inherited by child processes, so if your perl script had the dialogs disabled, and you started the excel instance from your perl script (rather than attaching to an existing instance), then Excel should inherit the same error mode setting as your perl script. Of course, there is nothing to stop Excel setting it back when it starts.

The SetErrorMode() API is exported by Win32API::File (Check the casing!). You can also get at it via Win32::API if that works for your installation. Or Inline::C if you have a compiler set up.

I don't have Excel, so I can't try this.

[Mike Rosenblum]

Hi Jocelyn,

I put together a reply to your post on the XBVT Forum that you posted. I would have replied directly here, but I don't know how to code in Pearl, unfortunately.

I think I did come up with a solution that should work for you, however.

Hope, hope...

Mike

[popx]

Thanks for that, BrowserUK. Unfortunately, when I tried SetErrorMode, it didn't help. Mike Rosenblum discussed this in general, in his extremely helpful postings #8 and #11 in the How to Catch An Excel Runtime Error From a WinForm? thread at Xtreme VB Talk. In #8, he says:

As you know, the problem is that VBA does not propagate any exception to the caller when a macro is called by the Excel.Application.Run method. Instead, Excel handles the error locally and displays an error message box to the user.

The only way around this is to not call the Excel.Application.Run method, period. No amount of attempting to tweak the API SetErrorMode() or the like will change anything, because, as far as the external caller is concerned, there is no error. There is no error because VBA handled it by displaying the error message box to the user. As far as I can see, there is no way around this.

So it looks as though this is something it's just not possible to do, via Perl or any other language. The best solution we have is suggested by Mike in #11. That's to call the macros from macros in a workbook object in another instance of Excel (these second macros are methods of the workbook object), and to call those macros from Perl. I show how I made it work in Java, in posting #12.