Excel 2013 - 1004 runtime error Refresh Query Table BackgroundQuery:=False

2
votes

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.

1
c#sqlexcelvbaexcel-2013
Isn't 1=0 always going to return zero results? - Automate This
Yes. That was a sample query so others could reproduce the issue we are seeing easily. - user2980800
Got it, I see what your say now. Sorry - Automate This
Could use a RefreshComplete event and run it in the background? - Dick Kusleika

1 Answers

2
votes

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.