Why not responding on Refresh BackgroundQuery?

Question

I would like to know on what wrong it is, Excel is hanged on following code forever.

Does anyone have any suggestions on how to solve this issue?

Thanks in advance for any suggestions

        With ws.QueryTables.Add(Connection:=qryConnect, Destination:=ws.Range("A1"))

        .name = "WebLink"

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertEntireRows

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = False 'True

        .RefreshPeriod = 0

        .WebSelectionType = xlEntirePage

        .WebFormatting = xlWebFormattingNone

        .WebPreFormattedTextToColumns = False 'True

        .WebConsecutiveDelimitersAsOne = True

        .WebSingleBlockTextImport = False

        .WebDisableDateRecognition = False

        On Error Resume Next

.Refresh BackgroundQuery:=False

        End With

Answer 1

The difference between BackgroundQuery:=True and :=False is as I said above.

With False the macro waits for the data to be returned.

With True the macro does not wait for the data to be returned - that happens asynchronously

So if you want to use the latest data later in the same macro sequence you need to use :=False but if you just want it refreshed as soon as possible but not be held up then :=True would be OK.

Some websites can take a while to respond. 

You said earlier that you have a problem when you are working on another workbook.

What is it that is causing this macro to run in that case?

Answer 2

When run with BackgroundQuery:=False it waits for the results to be retrieved.

You should do this if you wanted to use the results of the query in following code.

If you just wanted to get the latest data - e.g. you were running code like this on a timer every 5 minutes - then it would be better to use BackgroundQuery:=True which initiates the refresh but doesn't wait for the results to be retrieved.

Of course in the latter case you should not be remaking the querytable each time - and you could use the automatic refresh feature of the querytable.

I would like to know on what difference is between BackgroundQuery:=True and BackgroundQuery:=False, do I need to wait for the results to be retrieved if I just want to get the latest data?

On the other hands, would network busy cause this issue? It seems not likely, because it shows not responding for a long period of time. This issue occur a lot after I setup a new SSD to replace an old HHD, would it be the cause too?

Do you have any suggestions?

Thank you very much for any suggestions :>

Answer 3

I don't know of a reason why, with the same connect string, this would work when run on its own but would hang when "you are working on another program". 

QueryTable.Refresh simply runs the query specified for the QueryTable over the connection specified.

In your case you have not given a query so it just retrieves whatever it can from the URL you have provided as the Connection.

When run with BackgroundQuery:=False it waits for the results to be retrieved.

You should do this if you wanted to use the results of the query in following code.

If you just wanted to get the latest data - e.g. you were running code like this on a timer every 5 minutes - then it would be better to use BackgroundQuery:=True which initiates the refresh but doesn't wait for the results to be retrieved.

Of course in the latter case you should not be remaking the querytable each time - and you could use the automatic refresh feature of the querytable.

Answer 4

Since it hangs on the line which is executing the query to fill the querytable, it would seem likely that the problem lies in making the connection to the external data source. 

What does qryConnect contain?

When I only run this Excel alone without running any program, it works fine, but it often hangs on whatever I work on another program at the same time, do you have any suggestions on how Excel handles .Refresh BackgroundQuery issue?

querytable contains any URL, such as "URL;http://www.CNN.com"

Thanks you very much for any suggestions

Answer 5

Since it hangs on the line which is executing the query to fill the querytable, it would seem likely that the problem lies in making the connection to the external data source. 

What does qryConnect contain?