当使用PowerQuery刷新查询时，无法在VBA中捕获错误。

Question

我有一个工作簿，其中包含多个PowerQuery查询连接到其他工作簿。

每年在财年结束时需要更新此工作簿 - 这意味着查询需要指向新的工作簿。当然可以手动完成这些操作，但是有很多查询而且普通用户不知道如何操作（并且大部分PowerQuery功能对用户来说是故意隐藏的）。

因此，我编写了一些代码，允许用户选择指向新工作簿，然后该代码将使用新的源工作簿更新PQ公式。这一切都运行正常。

问题是，我需要确保用户选择了有效的工作簿，以便查询仍然能够工作。我的计划是简单地更新公式为新工作簿名称，然后运行"Refresh"，并捕获任何错误（如果刷新正常，则我的假设是所选工作簿是有效的）。

但是这里出现了问题。当我在VBA中使用".Refresh"方法时，没有报告任何错误。确实有一个用户对话框报告错误，但是VBA没有捕获任何信息-代码继续运行，就好像一切都很正常，也没有引发任何错误编号。

而且，该对话框不会响应Application.DisplayAlerts = False。它仍然显示，然后VBA代码继续运行，就好像什么也没有发生一样。

那么，有人知道是否有任何方法可以捕获此错误吗？FYI我用于"Refresh"的代码如下（简化版）：

Application.DisplayAlerts = False ' This does not stop the user dialog!
Dim conn as WorkbookConnection

Set conn = ThisWorkbook.Connections("MyQueryName")
conn.Refresh 'No error raised here, even if the Refresh fails
(there is however a user error dialog)

请注意，如果我使用conn.OLEDBConnection.Refresh，问题仍然存在。

根据微软的文档，我应该能够停止对话框，但是对我没有用（请参见https://msdn.microsoft.com/VBA/Excel-VBA/articles/workbookconnection-refresh-method-excel）。

谢谢！

- Jaspos

---

只需检查它是否是有效的工作簿。这方面有很多信息。您甚至可以打开工作表并检查它是否具有正确的列。 - Luuklag

@Luuklag - 完全理解这是可能的（事实上这是我正在使用的解决方法），但如果我能找到一个使用.Refresh方法的答案，代码将更容易维护，因为我不需要更新它，如果添加了任何新查询，这很可能会发生。 - Jaspos

也许有一个事件处理程序可以处理这种错误信息？ - Luuklag

弹出的对话框中显示了什么错误信息？这可能是Power Query中的一个错误。 - Alejandro Lopez-Lago - MSFT

对话框中错误的示例为： [Expression.Error] The key didn't match any rows in the table.但是这个错误是正确的 - 如果新选择的工作簿不包含正确的数据结构/表，那么它当然会出错。但我无法在VBA中捕获错误，这就是问题所在。 - Jaspos

Answer 1

两种选择：

1. 检查您的Power Query M代码中的错误。如果检测到错误，请输出不同的数据表，可能是一个仅包含错误代码的1x1表格。当出现此错误时，PQ刷新成功完成（就PQ而言），用户不会收到错误消息，您的VBA代码可以检查PQ是否生成了错误表格。

2. 使用QueryTable的AfterRefresh事件，如此处所述： 如何检查连接刷新是否成功

我以前用过#1。我刚刚发现了#2并尝试了一下。对我来说它有效，但它不能防止PQ错误消息的出现。

- Richard Wein