Forum Discussion

Kopec's avatar
Kopec
Icon for Helper I rankHelper I
4 years ago
Solved

Refresh specific query for PowerQuery by VBA

Hi,

I have a several queries from PowerQuery, and I need to refresh only specific one (by click on button).

I tried it by VBA but I don´t have a success:(.

Can you help me with correct VBA code?

Below you can see example, and link for download an experimental file.

https://uloz.to/tam/cd98e9e4-345d-41bc-b0b4-b8459aa86afc 

Honza

 

Need Help
  • Kopec's avatar
    Kopec
    4 years ago

    Corect syntax for refresh one specific Query by VBA is:

    SUB Macro_Name()
ThisWorkbook.Connections("Query – Name of Query").Refresh
End Sub

6 Replies

  • CarlosLemes's avatar
    CarlosLemes
    New Member
    2 years ago

    Some options for people who read this latter:

     

        'Delete or update queries
    ' 1 - alternative to refresh:
    ThisWorkbook.Queries("name_of_query").Refresh
    ' 2 - alternative to delete:
    ThisWorkbook.Queries("name_of_query").Delete
    ' 3 - alternative to loop through queries and take actions after finding:
    For Each qr In ThisWorkbook.Queries
        If qr.Name = "name_of_query" Then
            qr.Delete
        End If
    Next qr
    ' The end.

     

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Check out "DATA - Connections" for its real name in the file,

     

    or use such a snippet to traverse the whole Connections set,

     

    Sub DisplayNames()
    For Each qry In ThisWorkbook.Connections
        Debug.Print qry.Name
    Next qry
End Sub

     

    • Kopec's avatar
      Kopec
      Icon for Helper I rankHelper I
      4 years ago

      Corect syntax for refresh one specific Query by VBA is:

      SUB Macro_Name()
ThisWorkbook.Connections("Query – Name of Query").Refresh
End Sub
      • M001's avatar
        M001
        Icon for Helper I rankHelper I
        3 years ago

        Hi Kopec,

         

        I am using your code and replace the query name but keep getting the following error. Would you please enlighten me, thank you

         

        Run-time error '9'" 
Subscript out of range