VBA: Create PivotCache From WorkbookQuery

Question

I'm trying to create a PivotCache using a WorkbookQuery and I want to do this without loading my source data into the Workbook. I've done this before by using a WorkbookConnection, however, my data comes from a power query and I don't think I can create a WorkbookConnection from that. I've attached some code to illustrate the problem below:

Sub Main()
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim power_query As String
    power_query = "let in #table({""Name"", ""Score""}, {{""Betty"", 90.3}, {""Carl"", 89.5}})"

    Dim wbq As WorkbookQuery
    Set wbq = ThisWorkbook.Queries.Add("Example", power_query, "Example Data")

    Dim pvCache As PivotCache
    Set pvCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=wbq)

    Dim pvTable As PivotTable
    Set pvTable = pvCache.CreatePivotTable(TableDestination:=ws.Range("A1"))

End Sub

I've, also, tried using the OLE DB connection string that's generated from the new WorkbookQuery, pasted below, as my SourceData but it doesn't work either.

Dim src As String
src = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Example;Extended Properties="""""

Answer 1

It is almost same as my answer for your last question.

Use VBA to Output Excel Power Query to ListObject

Option Explicit

Sub PVTWithQuery()
    ThisWorkbook.Queries.Add Name:="Query1", Formula:= _
        "let in #table({""Name"", ""Score""}, {{""Betty"", 90.3}, {""Carl"", 89.5}})"
    ThisWorkbook.Connections.Add2 "Query - Query1", _
        "Connection to the 'Query1' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Query1;Extended Properties=""""" _
        , "SELECT * FROM [Query1]", 2
    ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ThisWorkbook.Connections("Query - Query1"), Version:=8).CreatePivotTable _
        TableDestination:=ActiveSheet.Name & "!R1C1", TableName:="PivotTable1", DefaultVersion:=8
    With ActiveSheet.PivotTables("PivotTable1")
        With .PivotCache
            .RefreshOnFileOpen = False
            .MissingItemsLimit = xlMissingItemsDefault
        End With
        With .PivotFields("Name")
            .Orientation = xlRowField
            .Position = 1
        End With
        .AddDataField .PivotFields("Score"), "Sum of Score", xlSum
    End With    
End Sub

Microsoft documentation:

Connections.Add method (Excel)