Refreshing PowerPivot Linked Table in Office 2010 from VBA

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!

Answer 1

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:

 

http://www.tomgleeson.ie/posts/201404/PowerPivotVBARefresh.html

 

 

---

Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

Answer 2

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?

Answer 3

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

Answer 4

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

Answer 5

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).

Answer 6

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