none
Power Query refreshes on open or via VBA RRS feed

  • Question

  • Data changes. How do I refresh my report built with Power Query as there is no "refresh on open" setting and no VBA support? Is VBA support likely in the near future. Thanks.

    Wednesday, August 28, 2013 4:01 PM

Answers

  • Hi,

    There's currently no VBA support in Power Query. This is something in our future roadmap but not near future yet (i.e. not in 2013). Something that you could try is using "Refresh All", which should refresh your queries as well.

    Thanks,
    M.

    Wednesday, August 28, 2013 10:37 PM

All replies

  • Hi,

    There's currently no VBA support in Power Query. This is something in our future roadmap but not near future yet (i.e. not in 2013). Something that you could try is using "Refresh All", which should refresh your queries as well.

    Thanks,
    M.

    Wednesday, August 28, 2013 10:37 PM
  • So Power Query is just "eye candy" and of no use in the real world. That's pretty sad.
    Wednesday, September 4, 2013 2:00 AM
  • Power Query creates Workbook connections that can be refreshed on open, and with VBA.

    This VBA will also refresh Power Query data:

    ActiveWorkbook.RefreshAll

    To refresh Power Query connection when you open the file, you can find the relevant Power Query-generated connections in the Workbook Connections dialog under Data->Connections, and configure the highlighted settings:

    Thursday, January 8, 2015 8:31 AM
  • Gil, this is not working for me. I have a PQ into an Excel file in the same folder. It refreshes on "Refresh all" but not on file open. Any ideas?

    Version: 2.20.3945.242


    cheers, teylyn


    Thursday, May 21, 2015 2:47 AM
  • Moreover, you can just refresh the Power Query connection. No need to refresh all connections.
    Friday, May 22, 2015 12:27 PM
  • That's not the point. I check the box to have the connection refreshed on File Open, but it does not refresh. That's the point. 

    cheers, teylyn

    Saturday, May 23, 2015 1:56 AM
  • If you're accessing the PowerQuery / Get & Transform through a Table (VBA ListObject) you can use the following VBA:

    Call ListObjects(tableName).QueryTable.Refresh(False)

    Simply make sure that you reference the appropriate ListObject. This will be using the Table Name that is linked to the query and this name may not necessarily be the same as your query name. The "False" indicates that it should not update in the background, you can change it to "True" if you're not concerned about that. I prefer "False" so that users can't interact until the data is ready when setting automated query updates.

    You can then place this in the Workbook_Open event to get it to trigger when the Workbook opens.


    Friday, January 20, 2017 10:42 PM