Excel automation via OLE - suppressing / catching dialog box errors?

Question

I refresh Excel 2007 data connections via a C# program and OLE. Most of the work is done by a single Workbooks.RefreshAll() statement.

As is the nature of refreshing spreadsheets, various things can go wrong. During the refresh process, the program can give dialog box error messages about "Data cannot be read from file '|'", and a message about "Overlapping pivottable reports". Both of these are fatal, and I should be able to catch these errors, and exit my program with an error.

Unfortunately, I don't seem to be able to catch these problems, and instead my automated program sits until I come along and hit enter on the dialog.

Does anyone know if it's possible to programmatically catch the errors shown in excel dialog boxes, instead of having them displayed to the user?

Answer 1

Your best bet is to set the Application.DisplayAlerts property to False. That is, assuming that your Excel.Application object variable is named "xlApp", all you'd have to do is the following:

xlApp.DisplayAlerts = false;

Note that this will cause the default response to be taken for each dialog box, which is normally what you would want. (There's no good way around this. Leaving DisplayAlerts = True and using SendKeys might be your only other option, but would be ugly and very error prone.)

Since you are making this call via OLE Automation, this call is cross-process, and, therefore, the DisplayAlerts setting will persist until you change it. (If called in-process, via VBA, it is switches back to to true automatically when the routine completes. The same behavior probably applies for a VB.NET or C# add-in called via a CommandBar or Ribbon control, but one would need to test to be certain.)