Create macro to update power query

[stephall]

I used power query to combine data from multiple sheets in multiple excel workbooks all within the same folder. I have a cell which i named Path that has the path to the folder. The query runs successfully and does what it's supposed to do. I however want to create a macro that can be used to assign to a clickable button to update the query easily. I keep getting a "Object variable or With block variable not set" error when i try to run the macro though. Please note that I don't really have much experience with VBA. Also If there is a way to assign a command to a button to update the query then that would be great.

The power query code is

let
Path = Excel.CurrentWorkbook() {[Name="Path"]}[Content]{0}[Column1],
Source = Folder.Files(Path)
in Source

 

[John_w]

I however want to create a macro that can be used to assign to a clickable button to update the query easily.

Put this code in a standard module and assign the macro to a command button on the sheet. Assumes the PQ is on the sheet named "Sheet1".

Public Sub Refresh_Power_Query()
    ThisWorkbook.Worksheets("Sheet1").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
End Sub

 

[stephall]

Put this code in a standard module and assign the macro to a command button on the sheet. Assumes the PQ is on the sheet named "Sheet1".

Public Sub Refresh_Power_Query()
    ThisWorkbook.Worksheets("Sheet1").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
End Sub

This works!
Thank you.
One thing that I would like to fix if possible is that after I run the macro and it refreshes the query, it highlights the selection area of the PQ table results on my main dashboard sheet. The Sheet1 that my PQ is on is hidden because that's just the source data and I don't actually want that to be displayed. So even though the dashboard sheet is the only sheet visible, the selection area highlighted on my dashboard sheet appears to be that of the PQ on Sheet1 and I have to manually click on my dashboard sheet tab for it to go away and return to the actual dashboard sheet. Is there a way to prevent this from happening?

 

[John_w]

Try this which selects the active sheet (the dashboard sheet, if the command button is on that sheet) after the refresh:

Public Sub Refresh_Power_Query()
    ThisWorkbook.Worksheets("Sheet1").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    ActiveSheet.Select
End Sub

 

[stephall]

Try this which selects the active sheet (the dashboard sheet, if the command button is on that sheet) after the refresh:

Public Sub Refresh_Power_Query()
    ThisWorkbook.Worksheets("Sheet1").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    ActiveSheet.Select
End Sub

Doesn't appear to have any effect, same result as before.

 

[John_w]

Try selecting a cell:

ActiveSheet.Range("A1").Select

 

[stephall]

Try selecting a cell:

ActiveSheet.Range("A1").Select

Same issue, I have to manually click on the sheet tab for the sheet to be active. Also when I try unhiding Sheet1 and then going back to the dashboard sheet and clicking the button to run the macro, it switches me over to Sheet1 and finishes on that sheet with the PQ selection results highlighted. When i click back over to the sheet with the dashboard it shows cell A1 as selected. So for some reason it seems to always be finishing on Sheet1 where the PQ is.

 

[John_w]

Maybe:

Public Sub Refresh_Power_Query()
    Dim currentSheet As Worksheet
    Set currentSheet = ActiveSheet
    ThisWorkbook.Worksheets("Sheet1").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    currentSheet.Activate
    currentSheet.Range("A1").Select
End Sub

 

[stephall]

Maybe:

Public Sub Refresh_Power_Query()
    Dim currentSheet As Worksheet
    Set currentSheet = ActiveSheet
    ThisWorkbook.Worksheets("Sheet1").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    currentSheet.Activate
    currentSheet.Range("A1").Select
End Sub

Still getting the same result everytime. It's no big deal though if we're unable to solve it since I don't want to take up too much of your time. I will just put a note to indicate that the user should click on the sheet tab after refreshing. Thanks for all the help by the way, I really appreciate it!