CODEWITHSUNDEEP
excelvbams-accesspowerquery
fuencislao
fuencislao

Reputation: 23

Auto refresh Excel Power Query when a particular cell changes (I am using VBA)

I am importing an Access database into an Excel workbook. I want to filter the table I am importing automatically based on the value of a cell in the Excel worksheet. I have been able to do that through Power Query code.

Next step is updating the table I am importing every time the cell value changes. I have been able to update the query every time any cell in the whole sheet (the one where the key cell is) changes using this code in VBA:

Private Sub Worksheet_Change(ByVal Target As Range)

    ThisWorkbook.RefreshAll

End Sub

I would like to edit that VBA code so that the Power query only updates when the particular cell that I am using to filter the table changes, not when any cell in the sheet changes.

Appreciate any tips!

Upvotes: 1

Views: 7900

Answers (2)

fuencislao
fuencislao

Reputation: 23

I ended up using this code which refreshes only the query I want instead of refeshing all queries in the workbook:

Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Me.Range("[Cell Reference]").Address _
Then

    ActiveWorkbook.Connections("Query - [Name of the query]").Refresh

End If

End Sub

In my case, [Cell Reference] was B5 and [Name of the query] was BS. The code must be added in the Sheet module where the cell or range (B5 in my case) is located.

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

not tested. Something like:

Private Sub Worksheet_Change(ByVal Target As Range)

if not intersect(cell_to_check, Target) is nothing then
    Application.EnableEvents = False
    ThisWorkbook.RefreshAll
end if

Application.EnableEvents = True

End Sub

Upvotes: 0

Related Questions