I refresh Excel 2007 data connections via a C# program and OLE. Most of the work is done by a single Workbooks.RefreshAll() statement.
我通过C#程序和OLE刷新Excel 2007数据连接。大部分工作都是由一个Workbooks.RefreshAll()语句完成的。
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?
有没有人知道是否可以以编程方式捕获excel对话框中显示的错误,而不是将它们显示给用户?
1 个解决方案
#1
9
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:
最好的办法是将Application.DisplayAlerts属性设置为False。也就是说,假设您的Excel.Application对象变量名为“xlApp”,您只需执行以下操作:
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.)
请注意,这将导致为每个对话框采取默认响应,这通常是您想要的。 (没有好办法解决这个问题。让DisplayAlerts = True并使用SendKeys可能是你唯一的另一种选择,但会很丑陋并且非常容易出错。)
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.)
由于您是通过OLE自动化进行此调用,因此此调用是跨进程的,因此,DisplayAlerts设置将一直存在,直到您更改它为止。 (如果在进程中调用,通过VBA,它会在例程完成时自动切换回true。相同的行为可能适用于通过CommandBar或Ribbon控件调用的VB.NET或C#加载项,但是需要测试以确定。)
#1
9
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:
最好的办法是将Application.DisplayAlerts属性设置为False。也就是说,假设您的Excel.Application对象变量名为“xlApp”,您只需执行以下操作:
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.)
请注意,这将导致为每个对话框采取默认响应,这通常是您想要的。 (没有好办法解决这个问题。让DisplayAlerts = True并使用SendKeys可能是你唯一的另一种选择,但会很丑陋并且非常容易出错。)
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.)
由于您是通过OLE自动化进行此调用,因此此调用是跨进程的,因此,DisplayAlerts设置将一直存在,直到您更改它为止。 (如果在进程中调用,通过VBA,它会在例程完成时自动切换回true。相同的行为可能适用于通过CommandBar或Ribbon控件调用的VB.NET或C#加载项,但是需要测试以确定。)