Programming Excel with VBA and .NET

Name

querytable.ResultRange

Synopsis

Returns the range containing the results of the query. For example, the following code clears the results from a query table on the active worksheet:

ActiveSheet.QueryTables(1).ResultRange.Clear

If a query table has been created but not yet refreshed, accessing ResultRange causes an error. There’s no direct way to test whether a query table has been refreshed. One solution to this problem is to write a helper function similar to the following to check if a query table has a result before accessing ResultRange elsewhere in code:

Public Function HasResult(qt As QueryTable) As Boolean
    Dim ret As Boolean
    On Error Resume Next
    Debug.Print qt.ResultRange.Address
    If Err Then ret = False Else ret = True
    On Error GoTo 0
    HasResult = ret
End Function

Now, you can easily test if a query table has a result before clearing the result range or performing other tasks as shown here:

Set qt = ActiveSheet.QueryTables(1)
If HasResult(qt) Then qt.ResultRange.Clear