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