Unable to create pivot table from Power Pivot data model using VBA

Question

Using Excel 2013, I am trying to create a pivot table in a workbook using the existing Power Pivot model using the PivotTables.Add method . There seems to be a problem with my PivotCache . Here is what I have done so far:

    Dim pc As PivotCache, i As Long
    '
    i = 1
    For Each pc In ActiveWorkbook.PivotCaches
      Range("a" & i).Value = pc.Index
      Range("b" & i).Value = pc.CommandText
      i = i + 1
    Next

Produces this:

1    Model
2    Model
3    Model

However, when you run below, a runtime error is thrown:

    Range("a1").Select
    ActiveSheet.PivotTables.Add _
    PivotCache:=ActiveWorkBook.PivotCaches(1), _
    TableDestination:=Range("A3")

Error:

Run-time Error '1004':
Application-defined or object-defined error

The error occurs with all three available PivotCache indexes (1-3).

FWIW, I can add a pivot table manually under the PowerPivot ribbon. Office> Home> Pivot table. I am trying to do the same in VBA. By the way, recording a macro does not record anything until I begin to manipulate only the created pivot table.

Any help would be greatly appreciated.

...

Answer 1

, - :

Sub xlPivotTable()

   Dim pc As PivotCache
   Dim pt As PivotTable

   'Create PivotCache from PowerPivot Data Model
   Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal, _
   SourceData:=ThisWorkbook.Connections("ThisWorkbookDataModel"), _
   Version:=xlPivotTableVersion15)

   'Create PivotTable from PivotCache
   Set pt = pc.CreatePivotTable(TableDestination:=ActiveCell, _
   DefaultVersion:=xlPivotTableVersion15)

   Set pt = Nothing
   Set pc = Nothing

End Sub

xlPivotTableVersion 14 Excel 2010, 15 Excel 2013 2016.