I am having a problem with executing a SQL query through a macro vs by simply clicking "Refresh" in excel 2010.
the set up:
- I open a new excel 2010 worksheet.
- go to the "Data" tab
- Under the "Get External Data" section I click "From Other Sources" and from that drop down I click "From Microsoft Query"
- I then choose the proper ODBC connection, cancel through the "Query Wizard" window and close the "Add Tables" window
- Then in the "Microsoft Query" window I simply click the "SQL" button, enter my query and hit ok
- 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.
- 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.
Best Answer
Here it is:
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
toTrue
.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),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.