how to add power query to specific table by code (1 Viewer)

[zezo2021]

How to add power query to specific table by code

I failed on all my tries

Thanks

 

[Minty]

It's pretty complicated from memory!
This was the start of one of mine

                xlWB.Queries.Add Name:=sTablename, Formula:= _
                    "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," _
                    & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""ProviderName"", type text},

Obviously the formula was initially created in the Excel Power query dialogue then adjusted and concatenated to accommodate what I was trying to achieve.

I did this about 4 years ago and remember it was quite an effort to get it to work, but it did what I need it to do.
I can't post any more of it as it belongs to a client.

sTablename was a variable used as the spreadsheet had multiple data sources, and multiple outputs all gathered into a complicated output structure.

This was all driven from Access recordsets in multiple loops, not done in Excel.

 

[LarryE]

You can create a new query using VBA:

Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim newSQL As String 
newSQL = "Select * From [employee_tbl] WHERE [Department]='HR'"
Set qdf = db.CreateQueryDef("tempQry", newSQL)

 

[zezo2021]

It's pretty complicated from memory!
This was the start of one of mine

                xlWB.Queries.Add Name:=sTablename, Formula:= _
                    "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," _
                    & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""ProviderName"", type text},

Obviously the formula was initially created in the Excel Power query dialogue then adjusted and concatenated to accommodate what I was trying to achieve.

I did this about 4 years ago and remember it was quite an effort to get it to work, but it did what I need it to do.
I can't post any more of it as it belongs to a client.

sTablename was a variable used as the spreadsheet had multiple data sources, and multiple outputs all gathered into a complicated output structure.

This was all driven from Access recordsets in multiple loops, not done in Excel.

not work
need alot of research from my side

I need to load Power query to special Excel Table by VBA

 

[zezo2021]

You can create a new query using VBA:

Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim newSQL As String
newSQL = "Select * From [employee_tbl] WHERE [Department]='HR'"
Set qdf = db.CreateQueryDef("tempQry", newSQL)

Excel Power Query
Excel Table

Not Access

 

[CJ_London]

Not my area of expertise but if you can do this manually through the interface, you should be able to record the actions into an excel macro which you can then edit as required

 

[Minty]

not work
need alot of research from my side

I need to load Power query to special Excel Table by VBA

Start by creating the power query you need in Excel manually.
You can then peek at the code using the advanced editor:

This code is what you would need to replicate in VBA using something similar to the syntax I posted earlier.
No one is going to build this for you without you creating the initial power query first, then trying it out I'm afraid.

 

[zezo2021]

Here is the macro convert Power Query To Table

Here is the Problem

Need to append to existing Table

Sub Macro3465656565665465()
'
' Macro3465656565665465 Macro
'

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

 

[zezo2021]

Here is the last edit
after editing

Sub Macro3465656565665465()

    ' Add Power Query to the MyTable table in the MyDatabase sheet
    Dim powerQuery As Object
    Set powerQuery = Application.Workbooks(ActiveWorkbook.Name).Sheets("MyDatabase").ListObjects("MyTable").QueryTable
    powerQuery.CommandType = xlCmdSql
    powerQuery.CommandText = Array("SELECT * FROM [Book1]")
    powerQuery.RowNumbers = False
    powerQuery.FillAdjacentFormulas = False
    powerQuery.PreserveFormatting = True
    powerQuery.RefreshOnFileOpen = False
    powerQuery.BackgroundQuery = True
    powerQuery.RefreshStyle = xlInsertDeleteCells
    powerQuery.SavePassword = False
    powerQuery.SaveData = True
    powerQuery.AdjustColumnWidth = True
    powerQuery.RefreshPeriod = 0
    powerQuery.PreserveColumnInfo = False
    powerQuery.ListObject.DisplayName = "MyTable"
    powerQuery.Refresh BackgroundQuery:=False
   
End Sub

the problem is append but replace old data

I need to append to last row in the table

any help

will be useful solution

@Minty
@arnelgp