Here's the story:
I have an Excel document that generates reports from a DB's contents. I have a form that collects the report parameters and that on the click of a button generates the report.
However the DB contents are huge and I also have a Cancel button to stop the report if at some point it's obvious it's going to take too long to generate...
I coded the Cancel button just fine. But if I hit it during the execution of one of the queries it just wouldn't Cancel until the query finished (which could be up to 5 min).
"Easy" - I thought - " just turn it into a background query so you can cancel it during execution". However the Refreshing property of the QueryTable doesn't seem to change until I hit Ctrl+Break to Debug the code and see why the query never finishes execution.
You can check this for yourselves: Just paste the following code on the ThisWorkbook module of a new workbook and change the query to one that will work on a DB of yours and hit F5 (since the ODBC connect string is incomplete it will prompt you to choose the ODBC connection)
Dim qt As QueryTable
Set qt = Application.ActiveSheet.QueryTables.Add("ODBC;", Range("A1"), "SELECT * FROM tabela")
qt.BackgroundQuery = True
qt.FieldNames = True
Do While qt.Refreshing
Even for a query that should last a second, it will be stuck... Hit Ctrl+Break to debug and "magically" the refreshing property goes to False and you'll get out of the loop...
By the way: I'm using the DoEvents method inside the loop since I need to capture the Click event on the Cancel Button. I've also tried adding a "Wait 1 sec" inside the loop but the result is the same.
What am I doing wrong here? And why is the Refreshing property behaving like that? What's the way to accomplish this task? I've looked everywhere but I've found nothing on the subject...