Create Table and populate with Power Query Connection

[bfreescott]

Hi guys

I cannot figure out how to Load my query data to a table. I've established the connection and pulled the data, but the last step of creating and populating the table is eluding me.

The recorder is no help since it simply refers to Selection.

Anyone have a code snippet of doing this step successfully in VBA?

 

[bobsan42]

Did you try sheet1.Listobjects("tablename").querytable.connection= ...
Unless i misunderstood something

 

[bobsan42]

I did it like this in my previous project (this is not the complete code - just the essentials):

    Select Case srcFN
        Case "" 'parametric formula - filename stored in a cell named dbFile
            qryFormula = "let" & Chr(13) & "" & Chr(10) & _
                "    dbFile = Excel.CurrentWorkbook(){[Name=""dbFile""]}[Content]{0}[Column1], " & Chr(13) & "" & Chr(10) & _
                "    Source = Excel.Workbook(File.Contents(dbFile), null, true)," & Chr(13) & "" & Chr(10)
        Case Else 'fixed filename
            qryFormula = "let" & Chr(13) & "" & Chr(10) & _
                "    Source = Excel.Workbook(File.Contents(""" & fn & """), null, true)," & Chr(13) & "" & Chr(10)
    End Select

            qryFormula = qryFormula & _
                "    tblPayments_Table = Source{[Item=""tblPayments"",Kind=""Table""]}[Data]," & Chr(13) & "" & Chr(10) & _
                "    #""Changed Type"" = Table.TransformColumnTypes(tblPayments_Table,{{""ID"", Int64.Type}, {""Account ID"", Int64.Type}, {""ReferenceNum"", type text}, {""PaymentBasis"", type text}, " & _
                " {""InvNum"", Int64.Type}, {""ProformNum"", Int64.Type}, {""PayDate"", type date}, {""CompanyID"", Int64.Type}, {""ClientID"", Int64.Type}, {""PaymentCurrency"", type text}, " & _
                " {""IBAN"", type text}, {""Credit"", type number}, {""Debit"", type number}, {""ExchangeRate"", type number}, {""CreditBGN"", type number}, {""DebitBGN"", type number}, " & _
                " {""EntryDate"", type datetime}, {""EntryUser"", type text}, {""LastModifiedDate"", type datetime}, {""LastModifiedBy"", type text}, {""DelayDays"", Int64.Type}, {""MoreInfo"", type text}})," & _
                Chr(13) & "" & Chr(10) & _
                "    #""Sorted Rows"" = Table.Sort(#""Changed Type"",{{""PayDate"", Order.Descending}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Sorted Rows"""

    With ThisWorkbook
        .Queries.Add Name:=tbName, Formula:=qryFormula
        Set wsh = .Worksheets.Add
        wsh.Name = tbName
    End With

    With wsheet.ListObjects.Add(SourceType:=xlSrcExternal, source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & tbName & ";Extended Properties=""""" _
        , destination:=wsh.Range("$A$1")).QueryTable
        .Parent.Name = tbName
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & tbName & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = tbName 
        .Refresh BackgroundQuery:=False
    End With

 

[bfreescott]

Thank you Bob... I sort of figured it out as you were posting your reply. My mistake was using my Connection variable as my Location instead of my Query variable. I also learned that if you use the Peek feature, the recorder is far more helpful than if you simply go through Load To option in the Quick Menu.