Excel 2013 - 1004运行时错误刷新查询表BackgroundQuery：= False [英] Excel 2013 - 1004 runtime error Refresh Query Table BackgroundQuery:=False

问题描述

当我将BackGroundQuery设置为False的QueryTable刷新时，我遇到了Excel 2013的问题（为了我们的目的，必须将BackgroundQuery设置为false）。当提供的查询不返回数据时发生1004运行时错误，没有返回数据的最常见原因是没有特定时间框架或特定资源的记录。

我的同事和我自己一直在努力解决这个问题，但没有找到解决方案，我们还没有发现任何内容，表明Excel处理刷新查询表的方式有所变化。

查看VBA中发生的一系列代码：

  Dim sql As String 
 sql =SELECT 1 WHERE 1 = 0
 Sheet1.QueryTables（1）.sql = sql 
 Sheet1.Activate 
 Sheet1.Range（b11 ）。选择
 Sheet1.QueryTables（1）.Refresh BackgroundQuery：= False 
  

刷新是当我们收到错误。将Where条件更改为1 = 1导致成功运行。

我们正在从C＃环境运行这些报告，我们必须等待数据填充我们保存报告。捕捉错误并继续也不是一个可以接受的解决方案，因为它是一个相当关键的一个非常普遍的错误。

此外，Excel 2007和2010没有这个问题。

对此问题的任何帮助将不胜感激。

解决方案

感谢大家的反馈和想法。我们发现一个工作对我们来说是相当低的影响。

我们发现这个问题只出现在Excel 2013中的查询表中，它们在QueryTable已刷新。

我们的工作简单地从具有QueryTable调用刷新功能的工作表中删除了BackgroundQuery设置为false，然后将过滤器应用于外部数据范围。 p>

示例：

  Sheet3.AutoFilterMode = False 
 Sheet3。 QueryTables（1）.Refresh BackgroundQuery：= False 
 Sheet3.Range（ExternalData_3）。AutoFilter 
  

这个解决方案可以满足我们的需求，我希望任何遇到这个问题的人都能找到这个有用的工作。

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.

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.

A sample bit of code to see what is happening in the 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.

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.

Also Excel 2007 and 2010 do not have this issue.

Any help on this issue would be much appreciated.

解决方案

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.

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.

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.

这篇关于Excel 2013 - 1004运行时错误刷新查询表BackgroundQuery：= False的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！