Answered by:
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!
- 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 SubAll 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- Proposed as answer by Fei XueMicrosoft employee Thursday, November 20, 2014 3:39 AM
- Marked as answer by Fei XueMicrosoft employee Tuesday, November 25, 2014 8:44 AM
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 modelpulling 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.comThursday, 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 listobjectsinto 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 ribboncustomisations the user has loaded.
Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.comMonday, 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 Subwhere "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 SubAll 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- Proposed as answer by Fei XueMicrosoft employee Thursday, November 20, 2014 3:39 AM
- Marked as answer by Fei XueMicrosoft employee Tuesday, November 25, 2014 8:44 AM
Tuesday, November 18, 2014 9:00 AM