Update Query Filter with VBA

[kaja30]

Hello,

Is it possible to write a VBA code that would change filter in the existing query. The code I gave so far is not working, mCode variable id returning code that is not the same as in the advanced editor in the Power Query Editor the source is different. When I run the query for the second time the mCode variable shows the filter changed but in reality the filter has changed. Please help

Sub FilterPowerQuery()
Dim wb As Workbook
Dim query As WorkbookQuery
Dim queryName As String
Dim NewCode As String
Dim mcode As String
Dim Source As String

On Error GoTo ErrorHandler
Set wb = ThisWorkbook
queryName = "Fruits"

Set query = wb.Queries(queryName)
mcode = query.Formula

NewCode = Replace(mcode, """Apples""", """Bananas""")

query.Formula = NewCode

Exit Sub

ErrorHandler:
MsgBox "An error occurred: " & Err.Description, vbExclamation

End Sub

 

[kaja30]

Hello,

Is it possible to write a VBA code that would change filter in the existing query. The code I gave so far is not working, mCode variable id returning code that is not the same as in the advanced editor in the Power Query Editor the source is different. When I run the query for the second time the mCode variable shows the filter changed but in reality the filter has changed. Please help

Sub FilterPowerQuery()
Dim wb As Workbook
Dim query As WorkbookQuery
Dim queryName As String
Dim NewCode As String
Dim mcode As String
Dim Source As String

On Error GoTo ErrorHandler
Set wb = ThisWorkbook
queryName = "Fruits"

Set query = wb.Queries(queryName)
mcode = query.Formula

NewCode = Replace(mcode, """Apples""", """Bananas""")

query.Formula = NewCode

Exit Sub

ErrorHandler:
MsgBox "An error occurred: " & Err.Description, vbExclamation

End Sub

Also query.Refresh is causing an Object defined error