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:
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,
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.
Post a Comment