No screen update after QueryTables.Add

Robert100

New Member
Joined
May 13, 2015
Messages
1
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:
Code:
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:
Code:
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
Code:
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
 
Last edited by a moderator:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top