VBA Get Number of records returned by Power Query

Brad McIntyre 101 Reputation points
2022-11-17T15:21:57.827+00:00

Thank you for taking the time to read my question.

I have some VBA that uses Power Query to call an API. Power Query calls the API then massages the JSON into a table which is returned to a table in my Excel file. I get errors when the API returns no data...

Question: How can I check the number of records returned by Power Query?

If I could do this, I could catch the code and avoid certain errors and my code failing. I usually get the error on the last line (.refresh)

I tried ActiveWorkbook.Queryies(QueryName). [nothing here referencing recordcount]
QueryName is a String variable
strQry is a String variable

ActiveWorkbook.Queries.Add Name:=QueryName, Formula:=strQry  
      
    ActiveWorkbook.Worksheets.Add  
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _  
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & QueryName & ";Extended Properties=""""" _  
        , Destination:=Range("$A$1")).QueryTable  
        .CommandType = xlCmdSql  
        .CommandText = Array("SELECT * FROM [" & QueryName & "]")  
        .RowNumbers = False  
        .FillAdjacentFormulas = False  
        .PreserveFormatting = True  
        .RefreshOnFileOpen = False  
        .BackgroundQuery = True  
        .RefreshStyle = xlInsertDeleteCells  
        .SavePassword = False  
        .SaveData = True  
        .AdjustColumnWidth = True  
        .RefreshPeriod = 0  
        .PreserveColumnInfo = True  
        .ListObject.DisplayName = "TheQuery"  
        .Refresh BackgroundQuery:=False  
    End With  
0 comments No comments
{count} vote