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!

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