Excel VBA：捕获不会刷新的外部数据连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20120587/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: Catching an external data connection that won't refresh?
提问by Peabody
Hello everyone and greetings from Germany!
大家好，来自德国的问候！
After searching for quite some time I am at my wits' end and I hope someone can help me.
在搜索了相当长的一段时间后，我不知所措，希望有人能帮助我。
I try to describe my problem as clear and briefly as possible:
我尝试尽可能清楚和简要地描述我的问题：
I am building a MS Excel 2010 Workbook that includes several (90+) external connections to SharePoint 2013 Lists & Libraries.
我正在构建一个 MS Excel 2010 工作簿，其中包括多个 (90+) 到 SharePoint 2013 列表和库的外部连接。
These connections were created by SharePoint's integrated "Export to Excel" function (in the List/Library-Ribbon) and the connection-files were then exported to another SP farm. (The first "source"-SP-Farm is from the customer, the second is our own intranet)
这些连接是由 SharePoint 的集成“导出到 Excel”功能（在列表/库-功能区中）创建的，然后连接文件被导出到另一个 SP 场。（第一个“来源”-SP-Farm来自客户，第二个是我们自己的内网）
I have to refresh these connections once per day via an automated macro.
我必须每天通过自动宏刷新这些连接一次。
A timer-job will open the workbook at night and execute the "RefreshAllConnections"-macro, that does a little bit more than just refreshing (such as writing the refresh date and time). So no user is present when this happens.
计时器作业将在晚上打开工作簿并执行“RefreshAllConnections”宏，这不仅仅是刷新（例如写入刷新日期和时间）。因此，发生这种情况时，没有用户在场。
And this is where my problem is:
这就是我的问题所在：
From time to time some of those connections cannot be refreshed. Excel displays an alert saying (translated from german):
有时，其中一些连接无法刷新。Excel 显示一条警告消息（从德语翻译）：
"the following data range could not be updated: owssvr (...) Do you wish to continue the update? (OK) / (Cancel)
“无法更新以下数据范围：owssvr (...) 您希望继续更新吗？（确定）/（取消）
What I found out so far:
到目前为止我发现了什么：
1) It's always the Library that is the problem
1）问题总是出在图书馆
2) It's rather random which Library won't update and when
2）哪个库不会更新以及何时更新是相当随机的
3) The problem fixes itself after some time (that's why I'm guessing this has something to do with the library being used/modified by someone else)
3）问题在一段时间后自行解决（这就是为什么我猜测这与其他人使用/修改的库有关）
4) While a library is refusing to update, using "Export to Excel" function again will prompt an error once the new worksheet is created and the data is supposed to be filled in
4）当库拒绝更新时，再次使用“导出到Excel”功能会在创建新工作表并应填写数据时提示错误
Now here are the odds:
现在是赔率：
1) The alert always uses the "old/original" connection name that was already changed by me.
1) 警报始终使用我已更改的“旧/原始”连接名称。
2) When I press OK the macro just continues in the next line, no error is thrown whatsoever
2) 当我按 OK 时，宏只是在下一行继续，没有任何错误被抛出
3) If I press cancel an Error 1004 occurs (which I can at least catch, so that would be okay).
3）如果我按取消，则会出现错误 1004（我至少可以捕捉到，这样就可以了）。
And here are the probs:
这是问题：
Since this is happening automatically at night, there is no user sitting near by to answer these alerts. So:
由于这是在晚上自动发生的，因此没有用户坐在附近来回答这些警报。所以：
1) The macro must automatically answer these alerts with "Cancel" IF they pop-up (and I have NO idea how to do that!)
1) 如果它们弹出，宏必须用“取消”自动回答这些警报（我不知道该怎么做！）
2) I disable them via "Application.displayAlerts = false
2）我通过“Application.displayAlerts = false”禁用它们
HOWEVER: this will automatically answer them with the default answer, which is "OK". This however is not throwing an error I could catch, so my macro won't now whether the update acutally worked or not.
但是：这将自动使用默认答案回答他们，即“确定”。然而，这并没有抛出我可以捕捉到的错误，因此无论更新是否有效，我的宏现在都不会。
Well that's about it. Sorry for the long post and thanks for reading.
嗯，就是这样。很抱歉这篇文章很长，感谢您的阅读。
Hopefully someone of you has an idea.
希望你们中有人有想法。
EDIT:
编辑：
Could it be that the automatically by SharePoint generated connections are the problem?
可能是 SharePoint 自动生成的连接有问题吗？
(How) can I build them myself?
（如何）我可以自己构建它们吗？
回答by Peabody
Well. I solved it myself.
好。我自己解决了。
The answer was stupidly simple:
答案非常简单：
Instead of refreshing the Connection, I now refresh the corresponding QueryTable via "ActiveSheet.ListObjects(1).QueryTable.Refresh"
我现在没有刷新连接，而是通过“ActiveSheet.ListObjects(1).QueryTable.Refresh”刷新相应的 QueryTable
If the Connection is not responding, an error is thrown that I can now catch properly.
如果 Connection 没有响应，则会抛出一个错误，我现在可以正确捕获该错误。
Ugh, finally!
嗯，终于！
