Use VBA to Output Excel Power Query to ListObject

Question

I've made a query using VBA and I want to output the result to a new table in Excel. Whenever I try to make the new table, however, Excel creates an empty table with column header "ExternalData_1: Getting Data ..." and never loads my query data.

Current Result of Creating New ListObject using VBA

At first I thought the WorkbookQuery object wasn't loading correctly, so I tried using its refresh method but it didn't work. I also thought the script kept concluding before the data could load, so I used the Application.Wait to give it more time but this didn't work either. It might be my OLE DB connection string, but I've looked at many examples and they all look like what I'm using. Below is the VBA code and an image showing what I'm trying to do.

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 src As String
    src = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Example;Extended Properties="""""
    
    Dim lst_object As ListObject
    Set lst_object = ws.ListObjects.Add(SourceType:=xlSrcQuery, Source:=src, Destination:=Range("A1"))

End Sub

Correct result should look like:

What I want VBA to output

Answer Update

Learning from the answers below, I decided to use the following code. The properties CommandType, CommandText and Refresh need to be changed in the QueryTable object that's part of the new ListObject in order for the query to load properly.

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 src As String
    src = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Example;Extended Properties="""""
    
    Dim query_table As QueryTable
    Set query_table = ws.ListObjects.Add(SourceType:=xlSrcQuery, Source:=src, Destination:=Range("A1")).QueryTable
    
    query_table.CommandType = xlCmdSql
    query_table.CommandText = "SELECT * FROM [Example]"
    query_table.Refresh BackgroundQuery:=False
    
End Sub

Answer 1

Following from Using data from WorkbookQuery directly in vba this worked for me

Sub Main()

    Dim ws As Worksheet, wb As Workbook
    Dim power_query As String, wbq As WorkbookQuery, lo As ListObject
    
    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets("Data")
    
    power_query = "let in #table({""Name"", ""Score""}, {{""Betty"", 90.3}, {""Carl"", 89.5}})"

    Set wbq = ThisWorkbook.Queries.Add("Example", power_query, "Example Data")
    
    Set lo = ws.ListObjects.Add(SourceType:=xlSrcModel, _
                                Source:=LoadToDataModel(wbq), _
                                Destination:=ws.Range("A1"))
    lo.Refresh

End Sub

'create and return a WorkBookConnection
Function LoadToDataModel(Query As WorkbookQuery) As WorkbookConnection
    Set LoadToDataModel = Query.Parent.Parent.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 Function