Can't create Pivot table from Power Pivot Data Model using VBA

Question

Using Excel 2013, I'm trying to create a pivot table in a workbook using an existing Power Pivot Model using the PivotTables.Add method. There seems to be a problem with my PivotCache. Here is what I've 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, running the below throws up a run time error:

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

The error is:

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 PivotTable manually under the PowerPivot ribbon. Manage > Home > PivotTable. I'm trying to accomplish the same thing in VBA. Incidentally, record macro doesn't record anything until I begin manipulating the PivotTable I just created.

Any help would be greatly appreciated.

Thanks...Josh

I've run your code & tried multiple variations in order to reproduce the error - I can't. The only error I've produced occurs when a pivot table would overlap a previous pivot table. Ensure that you're not overlapping. Change: TableDestination:=Range("A3") to something like "AA100".. if this works then you'll know this is causing the error. — Brian B.
Try using R1C1-style range references in your code, rather than the A1-style. This edit resolved a similar issue for me recently. — Mike
@Mike, it's not related to the issue. Have you tried above code? Brian B. is right. — Maciej Los
@MaciejLos : No, but I've generated the same error by manipulating A1-style range addresses in the same line that I've passed them to the Range() object, and I've successfully resolved it using the approach I suggested. I can't speak for the OP's machine, but this adjustment resolved a similar issue on mine. Beyond that, I used the comment section to suggest a potential improvement, and not to answer the question. — Mike

xlViki xlViki · Accepted Answer · 2015-11-15T13:55:01

Although it is too late for you, this might help someone else:

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

Change the xlPivotTableVersion to 14 for Excel 2010, 15 is for Excel 2013 and 2016.

Can't create Pivot table from Power Pivot Data Model using VBA

1 Answers