vba Excel 2013 - 1004 运行时错误刷新查询表 BackgroundQuery:=False
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19916824/
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 2013 - 1004 runtime error Refresh Query Table BackgroundQuery:=False
提问by user2980800
I have run into an issue with Excel 2013 when refreshing a QueryTable with BackGroundQuery set to False (BackgroundQuery has to be set to false for our purposes). When a query is provided that returns no data the 1004 runtime error occurs, the most common cause of no data being returned is that there are no records for a specific time frame or on a specific resource.
在使用设置为 False 的 BackGroundQuery 刷新 QueryTable 时，我遇到了 Excel 2013 的问题（出于我们的目的，BackgroundQuery 必须设置为 false）。当提供的查询不返回任何数据时，会发生 1004 运行时错误，不返回数据的最常见原因是特定时间范围或特定资源上没有记录。
My co-workers and myself have been trying to work around this issue but have found no solution yet and we have further found nothing that indicates that there was a change in how Excel handles refreshing query tables.
我和我的同事一直在尝试解决这个问题，但还没有找到解决方案，我们进一步发现没有任何迹象表明 Excel 处理刷新查询表的方式发生了变化。
A sample bit of code to see what is happening in the VBA:
一个示例代码，用于查看 VBA 中发生的情况：
Dim sql As String
sql = "SELECT 1 WHERE 1=0"
Sheet1.QueryTables(1).sql = sql
Sheet1.Activate
Sheet1.Range("b11").Select
Sheet1.QueryTables(1).Refresh BackgroundQuery:=False
On the Refresh is when we receive the error. Changing the Where condition to 1=1 results in a successful run.
刷新是我们收到错误的时候。将 Where 条件更改为 1=1 会导致运行成功。
We are running these reports from a C# environment in such a way we have to wait for data to populate and we save the report. Catching the error and continuing is also not an acceptable solution as it is a very generic error in a rather critical spot.
我们从 C# 环境运行这些报告，我们必须等待数据填充并保存报告。捕获错误并继续也不是一个可接受的解决方案，因为它是在相当关键的地方非常普遍的错误。
Also Excel 2007 and 2010 do not have this issue.
Excel 2007 和 2010 也没有这个问题。
Any help on this issue would be much appreciated.
对这个问题的任何帮助将不胜感激。
采纳答案by user2980800
Thank you all for the feedback and ideas. We found a work around that is fairly low impact for us.
感谢大家的反馈和想法。我们找到了一个对我们影响很小的解决方法。
We found that this issue was only present in Excel 2013 on Query Tables that have filters applied to them before the QueryTable was refreshed.
我们发现此问题仅出现在 Excel 2013 中的查询表中，这些查询表在刷新 QueryTable 之前应用了过滤器。
Our work around simply removed filtering from the sheet that has the QueryTable calling refresh with BackgroundQuery set to false then applying filters to our external data range.
我们的解决方法只是从具有 QueryTable 调用 refresh 且 BackgroundQuery 设置为 false 的工作表中删除过滤，然后将过滤器应用于我们的外部数据范围。
Example:
例子：
Sheet3.AutoFilterMode = False
Sheet3.QueryTables(1).Refresh BackgroundQuery:=False
Sheet3.Range("ExternalData_3").AutoFilter
This solution works for our needs and I hope anyone else who runs into this issue finds this work around useful.
此解决方案适用于我们的需求，我希望遇到此问题的任何其他人都会发现这项工作很有用。
