can not check querytable refreshed
WesNishi
1
Reputation point
I want to use querytable with BackgroundQuery = true.
I check querytable has finished to refresh to see that querytable.refreshing, but the value does not change from true to false. It can also be hooked in event of querytable_BeforeRefresh , but not in event of querytable_AafterRefresh. If I stop the debug by break point in a loop to check refreshing, .refreshing changes from true to false.
Does anyone know why?
Set g_QT = ActiveSheet.QueryTables.Add(Connection:=url, Destination:=destCell)
With g_QT
.RefreshStyle = xlOverwriteCells
.RefreshPeriod = 0
.SaveData = True
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.Refresh BackgroundQuery:=True
WAIT_RECEIVE:
Sleep QUERY_SLEEP_TIME
sleepCount = sleepCount + 1
If .Refreshing Then
If sleepCount <= QUERY_MAX_SLEEP_COUNT Then
GoTo WAIT_RECEIVE
Else
.CancelRefresh
.Delete
Exit Function
End If
Else
.Delete
End If
End With