VBA to Insert a Power Pivot Table

When Power Pivot was originally released by Microsoft, it was introduced as a Plug-In to Excel 2010.  After the second version of the Plug-In was built and released, the Microsoft Build team moved its focus to writing the product into the 2013 release of MS Office.  Unfortunately they ran out of time to write in all the features into the Office 2013 version – consequently there are a number of features that are actually better in Excel 2010 plug in version than the Office 2013 version.

In 2010, it is easier to edit your measures

right clickIn Excel 2010 you can right click on a measure in the Field List Window and select “EDIT”.  In 2013 you have to open the Manage Calculated Fields dialog and then find the measure you want to edit.

And 2010 is easier to insert a Power Pivot Table

missing button In Excel 2010 there is an “insert pivot table” button on the Power Pivot tab in Excel.  Just click on this button to create a new Pivot Table that is connected to the data model.  No such button exists in Excel 2013 (unless you go into the Power Pivot window.

The alternative to navigating to the Power Pivot window is you have to use 8 clicks to insert a table (Shown below).

insert pivot

The Solution – Some VBA to Add a Pivot Table

I have been thinking of writing some VBA code to replace this missing insert pivot table connected to the data model button from Excel 2013, and I got to it last week.  It is a very small amount of code that makes the process very easy.  Just copy the code into your personal.xlb workbook and then add a shortcut to the code in your quick launch menu.  You can then use this VBA to Insert a Power Pivot Table into any existing worksheet.

Edit: This was my original code

Sub InsertPPTable()
'
 'capture the starting cursor location
 myAddress = ActiveCell.Address
 myAddress = ActiveCell.Address
 X = ActiveWorkbook.PivotCaches.Count

 ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
 ActiveWorkbook.Connections("ThisWorkbookDataModel")).CreatePivotTable TableDestination:=Range(myAddress)
 Cells(1, 1).Select
 Range(myAddress).Select
End Sub

Improved code from Dominik

(see comments section for background)

Sub InsertPowerPivotTable()
   Dim PowerPivotCache As Excel.PivotCache
   Dim NewPowerPivotTable As Excel.PivotTable

   ‘ Create new cache
   Set PowerPivotCache = ActiveWorkbook.PivotCaches.Create( _
   SourceType:=xlExternal, _
   SourceData:=ActiveWorkbook.Connections(“ThisWorkbookDataModel”), _
   Version:=xlPivotTableVersion15)

   ‘ Create PivotTable
   Set NewPowerPivotTable = PowerPivotCache.CreatePivotTable( _
   TableDestination:=ActiveCell, _
   DefaultVersion:=xlPivotTableVersion15)

   ‘ Settings for new PowerPivotTable
   With NewPowerPivotTable
     .RowAxisLayout xlTabularRow
     .HasAutoFormat = False
   End With
   
   ‘ Cleanup
   Set NewPowerPivotTable = Nothing
   Set PowerPivotCache = Nothing
End Sub

Here is what it looks like to insert a Pivot Table linked to the data model when using my VBA code.

with macro

Share?

Comments

  1. Hi Matt,

    like your idea to create a PowerPivotTable via VBA. Great job!

    I really like your code and made some adjustments:
    • no need to store the activecell
    • no need to give the new table a name
    • added my favourite settings

    Here is the code:

    Sub InsertPowerPivotTable()

    Dim PowerPivotCache As Excel.PivotCache
    Dim NewPowerPivotTable As Excel.PivotTable

    ‘ Create new cache
    Set PowerPivotCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlExternal, _
    SourceData:=ActiveWorkbook.Connections(“ThisWorkbookDataModel”), _
    Version:=xlPivotTableVersion15)

    ‘ Create PivotTable
    Set NewPowerPivotTable = PowerPivotCache.CreatePivotTable( _
    TableDestination:=ActiveCell, _
    DefaultVersion:=xlPivotTableVersion15)

    ‘ Settings for new PowerPivotTable
    With NewPowerPivotTable
    .RowAxisLayout xlTabularRow
    .HasAutoFormat = False
    End With

    ‘ Cleanup
    Set NewPowerPivotTable = Nothing
    Set PowerPivotCache = Nothing

    End Sub

    Best regards from Germany
    Dominik.

    • I love the concept of “share, improve, reapply”. Thanks for adding your skill and experience to make this better. I have definitely learnt something about the Pivot Table/Cache objects from your improvement. I am going to update my code in the post and use your improved version.

  2. Hi Matt and Dominik

    Thanks both for the code! It isn’t working for me quite yet but I am still playing with it.

    What I wanted to ask was this. With a normal Pivot Table when you create one you need to be careful not to increase the number of Pivot caches or the workbook size bloats. Sometimes you have to have more than one cache if you need to group dates differently on one PT verses another. How does external data and that within PowerPivot work? Should we be seeking to use one cache? If so does this code create a new cache each time ?

    cheers
    John

    • Hi John

      Older versions of Excel had the pivotcache issue you describe, but not in newer versions. I am not 100% sure when it changed, but my guess is 2010+ should not have this issue.

      Are you using Excel 2010? If so the code you need to replicate this is likely to be different (i notice it doesn’t work in 2010). Try recording the process of adding a new pivot table attached to the data model, and the. Look at the code. My guess is it is different.

  3. Matt,

    I tried implementing this code today, but it keeps blowing up on the “SourceData:=ActiveWorkbook.Connections(“ThisWorkbookDataModel”), Version:=xlPivotTableVersion15)” line. The error is: Variable not defined. I’ve considered two possibilities: 1) are we supposed to be replacing the “ThisWorkbookDataModel” text with something else?, or 2) I am using Excel 2013, but the 64 bit version – does that mean the “Version15” portion should maybe be something else?

    Thanks, Randy Madden

    • Hi Randy. They are both good ideas but neither seem right to me. The easiest way to check is to record a new VBA macro and see what is recorded, then modify my code as needed. So turn on the recorder, then insert a new pivot table using the long method (I’ve using an external data source). There may be some slight difference with your version I guess.

      See how you go.

  4. Matt,

    Thanks for the reply. As near as I can tell it’s just Excel being weird (nothing new). Below I’ve posted Dominik’s code twice:

    Sub InsertPowerPivotTable()

    Dim PowerPivotCache As Excel.PivotCache
    Dim NewPowerPivotTable As Excel.PivotTable

    ‘Create new cache
    Set PowerPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
    ActiveWorkbook.Connections(“ThisWorkbookDataModel”), Version:= _
    xlPivotTableVersion15)

    ‘Create PivotTable
    Set NewPowerPivotTable = PowerPivotCache.CreatePivotTable( _
    TableDestination:=ActiveCell, _
    DefaultVersion:=xlPivotTableVersion15)

    ‘Settings for new PowerPivotTable
    With NewPowerPivotTable
    .RowAxisLayout xlTabularRow
    .HasAutoFormat = False
    End With

    ‘Cleanup
    Set NewPowerPivotTable = Nothing
    Set PowerPivotCache = Nothing

    End Sub
    Sub InsertPowerPivotTable2()

    Dim PowerPivotCache As Excel.PivotCache
    Dim NewPowerPivotTable As Excel.PivotTable

    ‘Create new cache
    Set PowerPivotCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlExternal, _
    SourceData:=ActiveWorkbook.Connections(“ThisWorkbookDataModel”), _
    Version:=xlPivotTableVersion15)

    ‘Create PivotTable
    Set NewPowerPivotTable = PowerPivotCache.CreatePivotTable( _
    TableDestination:=ActiveCell, _
    DefaultVersion:=xlPivotTableVersion15)

    ‘Settings for new PowerPivotTable
    With NewPowerPivotTable
    .RowAxisLayout xlTabularRow
    .HasAutoFormat = False
    End With

    ‘Cleanup
    Set NewPowerPivotTable = Nothing
    Set PowerPivotCache = Nothing

    End Sub

    The only difference is that, in the top one, that’s the code I recorded. The bottom one is cut and pasted directly off this page. I’ve looked it over a hundred times and I can’t see any difference besides where the line breaks occur. AND YET … the top one runs perfectly, and the bottom one won’t even compile. It gives me the same “Variable not defined” message. Go figure. Either way, I have a working version now, so thanks!

  5. Does “xlDataField” not work with PowerPivot? I’ve got the code below, which is used to add all measures to the pivot table. It works with “xlColumnField” and “xlRowField”. Any ideas how to make this work?

    Sub AddAllFieldsValues()
    Dim pt As PivotTable
    Dim iCol As Long
    Dim iColEnd As Long

    Set pt = ActiveSheet.PivotTables(1)

    With pt

    iCol = 1
    iColEnd = .CubeFields.Count

    For iCol = 1 To iColEnd
    With .CubeFields(iCol)
    If .Orientation = xlHidden Then
    .Orientation = xlDataField
    End If
    End With
    Next iCol

    End With

    End Sub

  6. Hi Shaun. You’re probably posting this on a dedicated help site, such as StackOverflow…diagnosing your issues might require quite a bit of back & forth in the comments, as it’s not entirely clear from your code what you want to accomplish. (For instance, not only is the syntax wrong, but it seems risky to assume that all hidden fields should be added to the Values area.)

    When troubleshooting VBA, the best place to start is to fire up the Macro Recorder, perform your action manually, then stop the Macro Recorder and go eyeball the code. When I add a preexisting Measure to the Values area, I get code like this:
    ActiveSheet.PivotTables(“PivotTable2”).AddDataField ActiveSheet.PivotTables( _
    “PivotTable2”).CubeFields(“[Measures].[Qualifier]”)

    When I add some non-measure field to the Values area (in which case Excel creates an implicit aka ‘Naked’ Measure…something not generally a good idea) then I get code like this:
    ActiveSheet.PivotTables(“PivotTable2”).CubeFields.GetMeasure _
    “[Table1].[Item Name]”, xlCount, “Count of Item Name”
    ActiveSheet.PivotTables(“PivotTable2”).AddDataField ActiveSheet.PivotTables( _
    “PivotTable2”).CubeFields(“[Measures].[Count of Item Name]”), _
    “Count of Item Name”

    As you can see, the syntax varies between these two different scenarios and is completely different to what you have above.

  7. Hi Matt,
    I am using VBA to create Datamodel and create relationship, unfortunately i am not able to achieve adding calculated columns in powerpivot table.

    here is my code sample….
    ActiveWorkbook.Connections.Add2 _
    “WorksheetConnection_Book1.xlsm!Table1”, “”, _
    “WORKSHEET;C:\Book1.xlsm”, _
    “Book1.xlsm!Table1”, 7, True, False

    ActiveWorkbook.Connections.Add2 _
    “WorksheetConnection_Book1.xlsm!Table2”, “”, _
    “WORKSHEET;C:\Book1.xlsm”, _
    “Book1.xlsm!Table2”, 7, True, False

    Dim Modl As Model
    Set Mod1 = ActiveWorkbook.Model

    ‘active relationship
    Mod1.ModelRelationships.Add Mod1.ModelTables(“Table1”).ModelTableColumns(“Worker ID”), Mod1.ModelTables(“Table2”).ModelTableColumns(“Supervisor ID”)

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x