locked
Refreshing PowerPivot Linked Table in Office 2010 from VBA RRS feed

  • Question

  • I have been fighting with this one, we have a excel report that use PowerPivot to connect to multiple data sources and using VBA a Refresh button that updates the data. This is working but there is a slight twist, we also have a Parameters sheet that the user can enter values that are used to calculate some of the columns, this is done using PowerPivot Linked tables.

    The problem is that the LinkedTable only get updated if the user does a 'Update All' under PowerPivot ribbon or open the PowerPivot Window. I need to in VBA get a hande to the LinkedTable and trigger a refresh before the rest of the refresh logic is excecuted.

    Some things I have tried:

    Sheets("Parameters").ListObjects("{LinkedTableName}").QueryTable.Refresh BackgroundQuery:=False

    Sheets("Parameters").Range("{LinkTableRange}").ListObject.QueryTable.Refresh BackgroundQuery:=False

    Sheets("Parameters").PivotTables("{LinkedTableName}").Update

    I can get a handle on the ListObject but everytime I get to the QueryTable it bombs out with Application-defined or object-defined error. It almost seeks like it want me to go through a database connection, but that does not make sense. My LinkTable is a local excel table so it should refresh into PowerPivot data without going anywhere.

    Any help will be appreciated!

    • Moved by George123345 Friday, November 14, 2014 7:49 AM Excel develop issue
    Thursday, November 13, 2014 2:30 PM

Answers

  • Thanks Mark but that also does not work, it run through without throwing an exception but if I refresh the PowerPivot tables after running this logic it still executes with the old Linked Table values.

    Sub RefreshLinkedTable(LinkedTableName As String)

        Dim wc As WorkbookConnection
        Set wc = ThisWorkbook.Connections(LinkedTableName)
        wc.Refresh
        Set wc = Nothing
        
    End Sub

    All of this is resolved in 2013 with the below code (Linked table as well as PowerPivot updates), I can't help to think they solved it by enhancing the DataModel rather than with VBA only.

    If Application.Version() > 14 Then
         ActiveWorkbook.Model.Initialize
            If lTable <> "" Then
               ActiveWorkbook.Connections(lTable).Refresh
            Else
            ActiveWorkbook.Model.Refresh
            End If                        
            Exit Sub
     End If

    Tuesday, November 18, 2014 9:00 AM

All replies

  • Hi Marius.vrstr,
     
    The code you tried so far is intended to update a listobject which is *pulling*
    data from an external source, not the other way around (a Powerpivot model
    pulling data from a listobject). That is far from easy with Excel 2010.
     
    See this web page for pointers on updating the powerpivot model:
     
     
     

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Thursday, November 13, 2014 2:55 PM
  • Thanks Jan, I am already using that approach to refresh PowerPivot data and then push it to Excel. It still however leaves me unable to refresh Excel to PowerPivot (Linked Table) before I refresh PowerPivot to Excel.

    Do you have any idea how to refresh the PowerPivot model from a list object (Linked Table) in VBA?

    Monday, November 17, 2014 10:49 AM
  • Hi MariusVorster,
     
    I'm sorry, I was under the impression that this would also update listobjects
    into the PP model!
     
    Perhaps a (very kludgy) sendkeys to send keys to the ribbon might do the trick,
    but it will probably depend on the add-ins and/or other files with ribbon
    customisations the user has loaded.
     
     

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Monday, November 17, 2014 11:04 AM
  • Thanks for the help so far. Will see if I can get some way around it, the environment is pretty strict any add-ons must go through rigorous approvals. I think the PowerPivot concept is PowerFull but feels like the execution and integration with excel is still lacking in many areas (Even in 2013 which is much better than 2010).

    Appreciate your input, will get back to you if I find a clean solution

    Monday, November 17, 2014 4:36 PM
  • Sub RefreshLinkedTable()
        Dim wc As WorkbookConnection
       
        Set wc = ThisWorkbook.Connections("LinkedTable_Name")
        wc.Refresh
        Set wc = Nothing
    End Sub

    where "LinkedTable_Name" is a linked table listed in the Workbook Connections dialog box (Ribbon navigation: Data > Connections group > Connections).

    Monday, November 17, 2014 8:45 PM
  • Thanks Mark but that also does not work, it run through without throwing an exception but if I refresh the PowerPivot tables after running this logic it still executes with the old Linked Table values.

    Sub RefreshLinkedTable(LinkedTableName As String)

        Dim wc As WorkbookConnection
        Set wc = ThisWorkbook.Connections(LinkedTableName)
        wc.Refresh
        Set wc = Nothing
        
    End Sub

    All of this is resolved in 2013 with the below code (Linked table as well as PowerPivot updates), I can't help to think they solved it by enhancing the DataModel rather than with VBA only.

    If Application.Version() > 14 Then
         ActiveWorkbook.Model.Initialize
            If lTable <> "" Then
               ActiveWorkbook.Connections(lTable).Refresh
            Else
            ActiveWorkbook.Model.Refresh
            End If                        
            Exit Sub
     End If

    Tuesday, November 18, 2014 9:00 AM