No screen update after QueryTables.Add

[Robert100]

Members,

Description: I like to process data from an internet page. I like to do it in the back ground because i can stop the procedure when the page is not available.
Problem: After if have checked "query Refreshing" is true and "query ResultRange.Rows.count <> 0" the query data is not yet displayed on the excel sheet
Question: 1) What im doing wrong ?
2) Why is the statement "If qTbl.Refreshing Then" not updating in the correct way ?

Question 1: What im doing wrong ?
Code:

Module Timer:

Private nTimer As Double
Public queryCount As Integer


Sub RunInterrupt()
DoEvents
'Check if the query is not finished
If qTbl.Refreshing Then
nTimer = Now + TimeValue("00:00:01")
'Count the number of times the query is not finished
queryCount = queryCount + 1
Application.OnTime nTimer, "RunInterrupt"
Else
queryFinished = True
End If
End Sub



Sub StopInterrupt()
Application.OnTime nTimer, "RunInterrupt", , False
End Sub

Module Internet:

Public qTbl As QueryTable


Sub GetInternetPage(url As String, _
bondsnummer As String, _
startPosition As String)



Cells.Select
Selection.QueryTable.Delete
Selection.ClearContents
Range("B2").Select
Set qTbl = ActiveSheet.QueryTables.Add(Connection:=url, _
Destination:=Range(startPosition))
With qTbl
.name = bondsnummer
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 1
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
End Sub

Main module

Public queryFinished As Boolean

Function FillDatabase(bondsnummer As String, _
inDatabase As Boolean) As Boolean
Dim a As String, _
rightBelowCorner As String, _
lastRow As String, _
saveSheetName As String
Dim queryFinished As Boolean

'Save the active sheet
saveSheetName = ActiveSheet.name
queryFinished = False
queryCount = 0
Sheets(FROM_SIDE_SHEET).Activate
'Activate a query to get data from an internet page
Call GetInternetPage("URL;http://publiek.mijnknltb.nl/Spelersprofiel.aspx?bondsnummer=" + bondsnummer, _
"Spelersprofiel.aspx?bondsnummer=" + bondsnummer + "_1", _
"$B$2")
'Activate a timer to check if the query is finished
Call RunInterrupt
'Delay loop to be sure the query is finished
While Not queryFinished
DoEvents
If queryCount = 10 Then
queryFinished = True
End If
Wend
'Stop the timer
StopInterrupt
Debug.Print "QueryFinished = " + CStr(CBool(queryFinished))
Debug.Print "qTbl.Refreshing = " + CStr(CBool(qTbl.Refreshing))
Debug.Print "Count = " + CStr(qTbl.ResultRange.Rows.count)
Debug.Print "queryCount = " + CStr(queryCount)

'Check if data is available from the interpage
If qTbl.ResultRange.Rows.count = 0 Then

result of the debug window:
QueryFinished = True
qTbl.Refreshing = True
Count = 1
queryCount = 10


At this point the function "ActiveSheet.QueryTables.Add" is successfull but the data is not displayed on the sheet. The query data appears on the screen only when i put a breakpoint on "If qTbl.ResultRange.Rows.count = 0 Then" or when the software is finished. So why dont excel update the sheet after the query is finished ?


Question 2: Why is the statement "If qTbl.Refreshing Then" not updating in the correct way ?

When i change the statement "While Not queryFinished" into While Not (queryFinished or qTbl.Refreshing) I get as result
QueryFinished = False
qTbl.Refreshing = True
Count = 1
queryCount = 1

Only now the subroutine "Run Interrupt" has been executed just one time instead of 10 times before the change. It seems to be the subroutine "Run Interrupt" with the statement "qTbl.Refreshing" is not updating in the correct way. How is that possible ? Also with this statement the excel screen is not updating.

Who can tell me what im doing wrong.

Kind regards,
Robbie100