How do you check if Excel QueryTable is refreshing or not?

[shwalk]

How do I handle URL connections - such as logging into a web application?
Example 1
When I use .Refresh BackgroundQuery:= False and there is a problem connecting, Excel hangs ... cannot Ctrl-Break ... only option is to kill Excel (and lose changes).
global QT As QueryTable
Set QT = ActiveSheet.QueryTables.Add(Connection:="URL;" & vLoginURL, Destination:=Range("A1"))
With QT
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebSingleBlockTextImport = False
.Refresh BackgroundQuery:= False
End With

Example 2
When I use .Refresh BackgroundQuery:= True, I need to give it time to refresh. Application.Wait, DoEvent, do while, etc don't let the querytable return data to the destination. So I use the following method:
Set QT = ActiveSheet.QueryTables.Add(Connection:="URL;" & vLoginURL, Destination:=Range("A1"))
With QT
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebSingleBlockTextImport = False
.Refresh BackgroundQuery:=True
End With
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "chkLogin"
Sub chkLogin()
If not(QT is Nothing) then
With QT
if .Refreshing Then .CancelRefresh
End with
End sub
Now, if it's still refreshing, it's ok. But if not, it gets stuck on .Refreshing with run time error 424.

Ideally I want to use .Refresh BackgroundQuery:= False
as I am calling a number of urls. I just need to provide an "out" when the url fails and hangs on .Refresh BackgroundQuery:= False

Any ideas?

 

[RoryA]

You can use the AfterRefresh event of the querytable if you declare a QT variable WithEvents (must be in a class module - worksheet code module or ThisWorkbook will do).

 

[shwalk]

Thanks for the advice. I tried the following, but it does not allow the query to return data to destination, so always fails. If I step through it, query only takes 2-3 seconds to complete - but of course, the final version cannot be stepped through.
ClassModule
=========
Public WithEvents objQT As QueryTable
Private errorState As Boolean
Private timeOut As Variant
Property Get GetTimeOut() As Variant
GetTimeOut = timeOut
End Property
Property Let SetTimeOut(ByVal inTime As Variant)
timeOut = inTime
End Property
Property Get HasErrored() As Boolean
HasErrored = errorState
End Property
Sub AssignQT(inQT As Object)
Set objQT = inQT
errorState = False
timeOut = Now + TimeValue("00:20:00")
End Sub
Sub doCancel()
If objQT.Refreshing = True Then
errorState = True
timeOut = ""
objQT.CancelRefresh
End If
End Sub

Module
=====
With ActiveSheet.QueryTables.Add(Connection:="URL;" & vLoginURL, Destination:=Range("A1"))
myQT.AssignQT inQT:=ActiveSheet.QueryTables(1)
myQT.SetTimeOut = Now + TimeValue("00:00:10")
.WebFormatting = xlWebFormattingNone
.Refresh BackgroundQuery:=True
End With
Do While myQT.GetTimeOut <> ""
If myQT.GetTimeOut < Now Then
myQT.doCancel
Exit Do
End If
Loop
If myQT.HasErrored Then
MsgBox "Unable to Login. Exiting Program."
Call ClearWorkSheet("tmp", "Y"): LoggedIn = False
Sheets("Home").Select: End
End If

 

[RoryA]

I meant more like this:

worksheet code module:

Option Explicit

Private WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
   If Success Then
      ' replace this with your code
      MsgBox "Success"
   Else
      MsgBox "Query failed"
   End If
End Sub
Public Sub MonitorQuery(oQueryTable As QueryTable)
   Set qt = oQueryTable
End Sub

Normal module:

Sub SetUpQuery()
   Dim strURL As String
   Dim qt As QueryTable
   
   strURL = "your URL here"
    
   Set qt = Sheet1.QueryTables.Add(Connection:= _
        "URL;" & strURL, Destination:=Range( _
        "B4"))
   ' hook into query events
   Sheet1.MonitorQuery qt
   ' refresh
    qt.Refresh BackgroundQuery:=True
End Sub

 

[RoryA]

I meant more like this:

worksheet code module:

Option Explicit

Private WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
   If Success Then
      ' replace this with your code
      MsgBox "Success"
   Else
      MsgBox "Query failed"
   End If
End Sub
Public Sub MonitorQuery(oQueryTable As QueryTable)
   Set qt = oQueryTable
End Sub

Normal module:

Sub SetUpQuery()
   Dim strURL As String
   Dim qt As QueryTable
   
   strURL = "your URL here"
    
   Set qt = Sheet1.QueryTables.Add(Connection:= _
        "URL;" & strURL, Destination:=Range( _
        "B4"))
   ' hook into query events
   Sheet1.MonitorQuery qt
   ' refresh
    qt.Refresh BackgroundQuery:=True
End Sub

 

[shwalk]

Thanks for suggestion. Two problems with this:
1. VBA must pause at qt.Refresh BackgroundQuery:=True until AfterRefresh occurs before continuing with next line of code.

2. What if AfterRefresh never happens? This is the key issue.

If I use qt.Refresh BackgroundQuery:=False, and query hangs, user has no choice but to kill Excel via task list. So I need a way to pause vba for a few seconds - without preventing query returning data to Destination (as executing for/do loops prevent) - and then cancel query if still running.

 

[RoryA]

1. Yes - that's the point. The remaining code needs to be triggered by afterrefresh, even if it merely sets a public variable.
2. Afterrefresh should, in theory, always occur - either with success set to true or false. I haven't tested it with failed web queries though.

 

[corydon]

I found the BackgroundQuery=True must be set. otherwise, if it set to False the afterfresh event will not be triggered. I got lost..., please someone help me on How to get the refresh status of each power query of a excel workbook via VBA?​

Thanks very much!