Excel VBA command to run Power Query

[kaz123]

Hi,

I wanted to find out if there is a way to run/invoke a Power Query using Excel VBA. I have a table of data which I then go onto Power Query manually to transform the data in the desired end state but wanted to see if there is a way to run this query in VBA so that when I assign a button to the macro/vba module it will automatically run the Power query. I am trying to create a spreadsheet for end users in which they can press a macro assigned button that will then transform the data using power query and wanted to see if there is away to avoid all the manual steps

Thanks in advance

 

[sandy666]

fortunately Power Query doesn't support vba in any way

 

[rjmdc]

how about something like this? i actually paid a VBA tech to create this for me
it works

Sub RefreshPQActiveWorkbook()

Dim ActSheet As String
ActSheet = ActiveSheet.Name

ThisWorkbook.Activate

If MsgBox("Do you want to refresh?", vbOKCancel + vbQuestion) = vbOK Then

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Call WSUnProtect(ws)
    Next ws

       ActiveWorkbook.RefreshAll
    
      
    For Each ws In ActiveWorkbook.Worksheets
        Call WSProtect(ws)
    Next ws
    
    Worksheets(ActSheet).Activate
    
    MsgBox "Refresh complete", vbInformation
End If

       Application.EnableEvents = True

End Sub

 

[kaz123]

how about something like this? i actually paid a VBA tech to create this for me
it works

Sub RefreshPQActiveWorkbook()

Dim ActSheet As String
ActSheet = ActiveSheet.Name

ThisWorkbook.Activate

If MsgBox("Do you want to refresh?", vbOKCancel + vbQuestion) = vbOK Then

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Call WSUnProtect(ws)
    Next ws

       ActiveWorkbook.RefreshAll
   
     
    For Each ws In ActiveWorkbook.Worksheets
        Call WSProtect(ws)
    Next ws
   
    Worksheets(ActSheet).Activate
   
    MsgBox "Refresh complete", vbInformation
End If

       Application.EnableEvents = True

End Sub

Thanks, this was helpful