VBA错误后如何继续？ [英] How Do I Continue After A VBA Error?

问题描述

我有一个程序有点黑客，但它是一个内部工具，它可以在99％的时间内工作。



它通过Windows Scheduler运行，它：



1）从我们的内部网站点下载电子表格

2）在电子表格中执行VBA方法以使用我们的内部SharePoint站点更新数据

3）它从生成的电子表格中删除一些数据



无法访问SharePoint时出现问题。我想在这种情况下清理并退出应用程序 - 问题最终将自行解决，并且一些故障不会让我们担心。



适用的代码是：

I have a program that's a bit of a hack, but it's an internal tool, and it works 99% of the time.

It runs via Windows Scheduler, and it:

1) Downloads a spreadsheet from our intranet site
2) Executes a VBA method in the spreadsheet to update the data using our internal SharePoint site
3) It scrapes some data from the resulting spreadsheet

My troubles occur when the SharePoint isn't reachable. I'd like to just "clean up" and exit the application in that case - the problem will eventually resolve itself, and a few failures aren't a worry for us.

The applicable code is:

Microsoft.Office.Interop.Excel.Application appExcel 
      = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = appExcel.Workbooks.Open(EXCEL_FILE);
Microsoft.Office.Interop.Excel.Worksheet ws = wb.Worksheets["MyWorksheet"];
appExcel.Application.Visible = false;
appExcel.Application.DisplayAlerts = false;
appExcel.Run("Refresh");

问题发生在最后一行。 VBA执行在对话框中停止：



运行时错误'1004'：

与SharePoint站点的连接不能是成立。要同步或刷新表，您必须能够连接到SharePoint站点。



发生这种情况时，我的应用程序仍然挂起直到我能够关闭它。我想关闭电子表格，关闭Excel并记录错误。



任何建议都将不胜感激。

The problem occurs on the last line. VBA execution halts with the dialog:

Run-time error '1004':
A connection to the SharePoint site cannot be established. To synchronize or refresh your table, you must be able to connect to the SharePoint site.

When this happens, my application remains "hung" until I'm able to close it. I would like to just close the spreadsheet, close Excel, and log the error.

Any suggestions would be greatly appreciated.

推荐答案

不幸的是，VBA几十年来没有现代技术中的异常处理;但VBA比旧款更具阻燃性。您只能使用 OnError 语句：

http://www.cpearson.com/excel/errorhandling.htm [ ^ ]，

https://support.microsoft。 com / en-us / kb / 141571 [ ^ ]。

-SA

Unfortunately, VBA does not have exception handling found in modern technologies for decades; but VBA is rather retardant thing than old. You can only use OnError statements:
http://www.cpearson.com/excel/errorhandling.htm[^],
https://support.microsoft.com/en-us/kb/141571[^].

—SA

这篇关于VBA错误后如何继续？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！