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

Question

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!

fuencislao · Accepted Answer · 2020-11-14 17:06:31Z

1

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.

answered Nov 14, 2020 at 17:06

fuencislao

231 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ron Rosenfeld · Accepted Answer · 2020-08-19 19:51:19Z

0

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

answered Aug 19, 2020 at 19:51

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

2 Comments

fuencislao Over a year ago

Thank you, Ron. I understand I would only need to change the "cell_to_check" part of your code, right?. I am not sure how to reference cells in VBA. I have checked a couple of ways but none of them seemed to work. The cell I want to reference is named as 'TeamNBA' and is on the A2 cell of the NBA sheet ("NBA!A2"). Sorry for my VBA naiveté.

Ron Rosenfeld Over a year ago

Many ways: Try Range("TeamNBA") or [TeamNBA] or ThisWorkbook.Worksheets("NBA").Cells(2,1) or ...

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related