Twitter Updates

    follow me on Twitter

    Friday, July 20, 2012

    PowerPivot Nuggets - Part 19 - Automate PowerPivot Refresh with Excel 2013

    Earlier this week Microsoft released the Office 2013 Customer Preview.  PowerPivot and Power View are now first class citizens in Excel, how cool is that.  I will blog about the BI features of Excel 2013 in the future.

    But today I want to share a nice long awaited feature for PowerPivot: Automate the refresh of the PowerPivot model.

    Excel 2013 now contains a Workbook.Model class that can be accessed via VBA code.  The following line of code will refresh your PowerPivot Model.

    Me.Model.Refresh

    If you want to automatically refresh the PowerPivot model when the workbook is opened, simply follow the following 2 steps.

    • Press ALT-F11 to open the Vusual Basic editor and then paste the following lines of code in the Code window for ThisWorkbook

      Private Sub Workbook_Open()
          If MsgBox("Do you want to refresh the PowerPivot Model?", vbYesNo) = vbYes Then
              Me.Model.Refresh
          End If
      End Sub
    •  Save the workbook as an Excel Macro Enabled Workbook (.XLSM)

     Next time you open the Excel file you will be prompted with the question "Do you want to refresh the PowerPivot Model".  Choose Yes, and Excel will automatically refresh the PowerPivot Model and all the Pivottables in the workbook.

    How cool!!!

    3 comments:

    Rafael Alencar said...

    Not working perfect, the data model is fully updated after excel start, but when i use the macro it dosent refresh, it loads or calculate some data because it takes time, but dont refresh the data model with new data dont know why.. I am using only a workbook connections OLEDB for powerpivot.

    Rafael Alencar said...
    This comment has been removed by the author.
    Frederik Vandeputte said...

    Rafael,

    So far I tested it with linked tables, TXT files, Access and SQL Server (SQLNCLI11). It seems to work most of the time but occasionally it hangs. I guess it will become more stable by the RTM time.