May 19 2020 05:21 PM
May 19 2020 05:21 PM
I have created a spreadsheet which is uses power query to extract text from a cell. This is information is updated each day, is there a way i can use a macro so that the power query function will refresh when new data is added, or a button is enabled.
Thank you kindly for any assistance.
May 19 2020 09:31 PM - edited May 19 2020 09:33 PMSolution
Power Query converts the source data into an Excel Table if the source data range is not formatted as an Excel Table and this table has a name. Let's assume that name of the source Excel Table is "Table1" and it is on the Sheet1.
Let's also assume that the name of the Query Connection is "Query - Table1" which you want to refresh automatically whenever the data in the Table1 gets changed. Refer to the screenshot below to know how to get the correct name of the Query Connection to update the Code that would be used to refresh the Query automatically.
Now if the source table "Table1" is placed on Sheet1. right click the Sheet1 Tab ans choose View Code and then place the code given below into the opened code window.
Private Sub Worksheet_Change(ByVal Target As Range) Dim tbl As ListObject 'Name of the source Excel Table on the worksheet, Power Query imports the data from Set tbl = ActiveSheet.ListObjects("Table1") If Not Intersect(Target, tbl.DataBodyRange) Is Nothing Then 'Replace the "Query - Table1" with the name of the query connection to refresh ThisWorkbook.Connections("Query - Table1").Refresh End If End Sub
The above code is for Sheet Change Event and that means whenever you change the data in the Table1, the "Query - Table1" will be refreshed automatically.
Pay attention to the comments added in the code to know how you can tweak this code to make it work for you.
May 20 2020 05:50 PM
Thank you kindly for your help.
I will have a go at updating my spreadsheet shortly.
Jul 23 2021 12:38 PM
how would one go about having this refresh if the data source was multiple tables on multiple sheets?
Jul 23 2021 08:17 PM