Using data from WorkbookQuery directly in vba

Question

I have an existing data connection in my excel work book.

I have come across code that can query against that data connection while loading it to a worksheet:

With currentSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdDefault
        .CommandText = Array("SELECT * FROM [" & query.Name & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=False
    End With

source https://gallery.technet.microsoft.com/office/VBA-to-automate-Power-956a52d1

via How to automate a power query in VBA?

And also this from the same source, which is essentially the same thing:

Sub LoadToWorksheetAndModel(query As WorkbookQuery, currentSheet As Worksheet) 
    ' Let's load the query to the Data Model 
    LoadToDataModel query 
     
    ' Now we can load the data to the worksheet 
    With currentSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _ 
        Connections("Query - " & query.Name), Destination:=Range("$A$1")).TableObject 
        .RowNumbers = False 
        .PreserveFormatting = True 
        .PreserveColumnInfo = False 
        .AdjustColumnWidth = True 
        .RefreshStyle = 1 
        .ListObject.DisplayName = Replace(query.Name, " ", "_") & "_ListObject" 
        .Refresh 
    End With 
End Sub 
 
Sub LoadToDataModel(query As WorkbookQuery) 
     
    ' This code loads the query to the Data Model 
    ThisWorkbook.Connections.Add2 "Query - " & query.Name, _ 
        "Connection to the '" & query.Name & "' query in the workbook.", _ 
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _ 
        , """" & query.Name & """", 6, True, False 
 
End Sub 

But this loads the data to a worksheet, rather than keeping the data in a VBA object.

I would like to efficiently query against this connection and load the result to a variable for further use in VBA without it ever touching a worksheet.

Specifically, I would like to be able to do things like pull all unique values from a column in the data connection into a variable. And I would like to further filter the data in the connection before writing it to a worksheet (while preserving the original data so it can be re-queried).