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.
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
- 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.