Excel 2010 freezes when executing SQL statement through macro

Question

I am having a problem with executing a SQL query through a macro vs by simply clicking "Refresh" in excel 2010.

the set up:

1. I open a new excel 2010 worksheet.
2. go to the "Data" tab
3. Under the "Get External Data" section I click "From Other Sources" and from that drop down I click "From Microsoft Query"
4. I then choose the proper ODBC connection, cancel through the "Query Wizard" window and close the "Add Tables" window
5. Then in the "Microsoft Query" window I simply click the "SQL" button, enter my query and hit ok
6. It executes the query in the "Microsoft Query" window, then I close that window, select the cell I want it to populate in when prompted and hit ok.
7. The query is then populated in my spreadsheet.

Here is where the issue comes up:

If I right click on the table and select "Refresh" from the right-click pop up menu the data will refresh and the little spinning globe at the bottom of the screen which says "Running background query..." will appear and spin until the query has finished running and excel will not freeze and I can work in other tabs or other worksheets.

However, if I execute that exact statement through a macro:

Range("A6").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

The spinning globe does not appear, excel stops responding, sometimes goes white, and the mouse turns into the spinning "not responding" circle when hovered over excel. At this point I am unable to work in excel at all until the query finishes executing and excel starts responding again.

This is an issue because for most of my reports I use cells in excel to allow users to insert parameters into the SQL statements that I execute.

Example:

Dim oQuery As QueryTable
Dim oDate As String

Set oQuery = ActiveWorkbook.Sheets("Sheet1").ListObjects(1).QueryTable

oDate = Range("B1")

oQuery.CommandText = "exec database.dbo.ExampleProcedure @SuppliedDate = '" + oDate + "'"

oQuery.Refresh False

This was not an issue in Excel 2003, but only came up when I switched to 2010 and had to change the query table vb code as shown in the example below

in 2003:

 Set oQuery = Sheet1.QueryTables(1)

in 2010:

Set oQuery = ActiveWorkbook.Sheets("Sheet1").ListObjects(1).QueryTable

Also it only happens on about 75% of the reports I write and is only a noticeable issue when the SQL statements takes more than a few seconds to refresh. This is becoming a pretty big issue, so I'd be extremely grateful to anyone who could help. Thanks so much.

Solution

Here it is:

BackgroundQuery:=False 

This means "Don't let anyone do anything until the query has finished".

Excel is doing what you asked. If you want people to be able to continue, you need to change False to True.

If you need to perform further processing after the query, you have two options (apart from speeding up the queries which I assume you have already done).

Either use a DoEvents loop while you wait for the query to complete (check QueryTables.Item(1).Refreshing to see if it is still running),

Sub RefreshQueryAndWait(ByVal oQuery As QueryTable)
  '' Assume oQuery is the current query
  oQuery.Refresh BackgroundQuery:=False
  Do While oQuery.Refreshing
    DoEvents
  Loop
End Sub

Or: put the second half of the VB code (after the refresh) into a different Sub which you call when the query completes.

See http://support.microsoft.com/kb/213187 for how to do this.