Change power query source for a specific table name at the same workbook by using VBA

[RotemShimoni]

Hello,

I have an excel template with macro that have a 2 sheets:
The first contain Table and the second Power Query to this table.
I made a button for duplicate the sheet with the table and I want to make a new button that will change the source of the Power Query from the old table to the new one.
** I don't know the new table name because the user can duplicate as mush as he want (normally not more then 5)

Thanks a lot for every help.

Rotem

 

[RotemShimoni]

Hey.. I solve the problem by cancelling the power quarry and put VBA code instead.

 

[smozgur]

Glad to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[RotemShimoni]

OK. Thank you about your comment.
I will be glad if someone will solve this problem because I didn't succeed in this case.

 

[smozgur]

The first contain Table and the second Power Query to this table.
I made a button for duplicate the sheet with the table and I want to make a new button that will change the source of the Power Query from the old table to the new one.
** I don't know the new table name because the user can duplicate as mush as he want (normally not more then 5)

Do you want to duplicate the sheet with the table in the same workbook, then change the Power Query source to be set as the copied table in the new sheet?
If this is the case, then why do you need a second button? In this case, then it is simply copying the worksheet and changing the query formula to use the new table name in the new worksheet. Something like this:

Sub doIt()
    ' Copy the TableSheet - assuming the sheet name is "TableSheet"
    ThisWorkbook.Worksheets("TableSheet").Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    
    ' Change the Power Query source table - assuming the query name is "Query1"
    ThisWorkbook.Queries("Query1").Formula = _
        "let " & _
        "  Source = Excel.CurrentWorkbook(){[Name=""" & ActiveSheet.ListObjects(1).Name & """]}[Content]" & _
        "in" & _
        "  Source "
End Sub

This code will basically change the following M Code (the source code that you see in the advanced view in Power Query - formula of the query):

let   
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
in  
    Source

with the following one:

let   
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content]
in  
    Source

This is just a sample, in a real situation the existing M code could be more than this but simply changing "Table1" to "Table2" instead of knowing and rewriting the entire M code (or whatever the new table name is that is found automatically in the VBA code) would be a better approach. Just like below by using the Replace function in VBA.

Sub doIt()
    ' Copy the TableSheet
    ThisWorkbook.Worksheets("TableSheet").Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    
    ' Replace the Power Query source table name in the existing M code
    ' We already know the query name
    ThisWorkbook.Queries("Query1").Formula = Replace(ThisWorkbook.Queries("Query1").Formula, _
        "Name=""Table1""", _
        "Name=""" & ActiveSheet.ListObjects(1).Name & """")
End Sub

If this doesn't help, then perhaps you could explain the use case in more detail, so I can understand how the user is going to use the workbook. Then I would like to help as much as I can, and I believe others too.

 

[RotemShimoni]

First of all thank you very much! I appreciate your comment.
My case was that I have a table source for my quarry and I don't know what is the name of the table (because the user can duplicate this table as much as he want). I wanted to switch the quarry source according to the active sheet that contains the table that the user choose.
I solve it by using a code for rename the table before the refreshing the quarry.
The quarry source point to "BestTable".

Sub RenameTable()
With ActiveSheet
.ListObjects(1).Name = "BestTable"
End With
End Sub

My problem was that I can't affect M code with VBA.

 

[smozgur]

Good to hear you found a working solution.