none
thisworkbook.refreshall. Refreshes excel table, doesn't refresh linked powerpivot model table

    Question

  • I have an excel table that's populated using Power Query from the web.

    I've added this table to my data model as a linked table.

    I've added a button that drives a simple macro that has a thisWorkbook.RefreshAll call at the end (there's some filters that need to be set beforehand that drive which URL is used to source the data)

    This call runs the power query that refreshes the excel table, but the linked table doesn't refresh unless i open powerpivot. It then refreshes automatically.

    Why is this? Am I missing something? I have multiple tables in my data model sourced via different means and the result is an out of sync data model. It's only this Power Query table that isn't getting refreshed.


    Jakub @ Adelaide, Australia

    Friday, January 17, 2014 4:21 AM

Answers

  • Hey Jakub, having trouble with LinkedTables and refreshing them through VBA as well. For me this is a bug more than anything else, if the Linked Table's update mode is set to Automatic and it does not refresh (Unless you manually trigger it by opening or selecting refresh all) and there is no way of triggering it from VBA there is a big problem that must be fixed.
    Thursday, November 13, 2014 2:40 PM
  • ...I'm curious to know what "one liner" solution poster MariusVorster is talking about above... Jakub

        Application.ScreenUpdating = False
            
        If Application.Version() > 14 Then
            ' Wake up Model
            ActiveWorkbook.Model.Initialize
            
            If lTable <> "" Then
               ActiveWorkbook.Connections(lTable).Refresh
            Else
            
            ActiveWorkbook.Model.Refresh
            End If
                             
            Exit Sub
        End If
    The one liner is the actual Model.Refresh but this only works in Office 2013

    • Marked as answer by jakubk Friday, February 13, 2015 6:05 AM
    Thursday, February 12, 2015 2:50 PM
  • For bugs, please submit them to the team...

    PP Addin - SQL Server Connect

    PP for E2013 - Excel Smile Frown

    PBI for O365 - Smile Frown

      

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Saturday, December 13, 2014 1:03 AM
    Owner

All replies

  • Hi Jakub,

    Thank you for your question. I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, January 21, 2014 7:58 AM
    Moderator
  • Hi,

    Your question falls into the paid support category which requires a more in-depth level of support. Please visit the below link to see the various paid support options that are available to better meet your needs.

    http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone 


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Jun Zh - MSFT Microsoft Online Community Support

    Wednesday, February 05, 2014 7:48 AM
  • I didn't realise that the 'refresh' button in excel not working was a paid support issue

    I think i'll leave it for now and plan my "PowerBI" around not using power query + power pivot together where the data source isn't static


    Jakub @ Adelaide, Australia

    Wednesday, February 12, 2014 3:54 AM
  • Hey Jakub, having trouble with LinkedTables and refreshing them through VBA as well. For me this is a bug more than anything else, if the Linked Table's update mode is set to Automatic and it does not refresh (Unless you manually trigger it by opening or selecting refresh all) and there is no way of triggering it from VBA there is a big problem that must be fixed.
    Thursday, November 13, 2014 2:40 PM
  • For bugs, please submit them to the team...

    PP Addin - SQL Server Connect

    PP for E2013 - Excel Smile Frown

    PBI for O365 - Smile Frown

      

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Saturday, December 13, 2014 1:03 AM
    Owner
  • Are you using 2010 or 2013? Excel 2010 does not expose the Power Pivot API via VBA so you have to hack around it. In 2013 it should work pretty seamlessly.

    There are a number of instances of Power Pivot not refreshing properly if fed from Power Query and other data sources simultaneously.

    Tuesday, December 16, 2014 12:24 AM
  • It was excel 2013 - I haven't opened that workbook in nearly a year (not sure if i still have it either).

    It was a concurrency issues i believe. The refresh all was firing the ppvt to refresh from the linked table, but the linked table hadn't been updated by the PQ yet.

    So hitting refresh would appear to update the worksheet that held the linked table, but the powerview and pivot tables/charts that were driven by the model were still showing the old data. I had to open the powerpivot window to get it to refresh.


    Jakub @ Adelaide, Australia Blog

    Wednesday, December 17, 2014 12:14 AM
  • I spent too many hours on this same issue, in short the above is the best summary I have seen. If you are using 2013 its basically a one liner. However in 2010 there is no clean way of updating linked table from VBA, my solution was making it very visible with a normal flattened PowerPivot table as well. If the two values are out of sync a big red warning appears telling the user what needs to happen. For good usability it must either be seamless or the manual step must be clear and guided. Very disappointed in PowerPivot 2010 feels like it is incomplete and integration into excel is hacky at best.

    Wednesday, February 04, 2015 8:08 PM
  • Hi Jakub,

    Can you check if a second RefreshAll command in the Macro does the job without the need to open the PowerPivot dialog?

    We are aware of the issue and will investigate how to fix it.

    Thank you,

    Gil

    Thursday, February 05, 2015 9:55 AM
  • Hi Jakub

    We investigated the issue more and it seems that there is an inherent problem with Excel query tables are linked to the data model.

    The best solution would be to target the query directly to the data model.

    What is the reason that made you decide to go thru an Excel table ?

    Dany Hoter

    Excel PM

    Thursday, February 05, 2015 7:28 PM
  • Hi Dany,

    probably because it was the default back then :) in fact i dont think i knew it was able to go directly into the data model.

    And to answer your earlier question, i think i had a few RefreshAlls in sequence with maybe even a delay timer (I don't remember anymore, it was a while ago and the workbook is on a different PC) but it didn't work reliably.

    I'm curious to know what "one liner" solution poster MariusVorster is talking about above

    But yes, since then, if it's something that needs to be refreshed then it usually goes into the data model.

    The only exception is when I'm still designing my model and will probably need to play with the table structure in the power pivot model. Some changes silently convert the PQ connection into 'read only' and if that happens i'd need to blow the table away and start from scratch - https://cwebbbi.wordpress.com/2014/09/08/power-pivot-power-query-read-only-connection-problems-in-excel-2013-and-what-to-do-about-them/


    Jakub @ Adelaide, Australia Blog

    Thursday, February 05, 2015 10:38 PM
  • ...I'm curious to know what "one liner" solution poster MariusVorster is talking about above... Jakub

        Application.ScreenUpdating = False
            
        If Application.Version() > 14 Then
            ' Wake up Model
            ActiveWorkbook.Model.Initialize
            
            If lTable <> "" Then
               ActiveWorkbook.Connections(lTable).Refresh
            Else
            
            ActiveWorkbook.Model.Refresh
            End If
                             
            Exit Sub
        End If
    The one liner is the actual Model.Refresh but this only works in Office 2013

    • Marked as answer by jakubk Friday, February 13, 2015 6:05 AM
    Thursday, February 12, 2015 2:50 PM