OK folks I have not tried this out yet myself but it deserves to be spread far and wide:
Tom Gleeson appears to have found a live specimen of Bigfoot itself: the ability to refresh PowerPivot data from VBA.
Full post:
[link removed due to 404]
Rob Collie
One of the original engineering leaders behind Power BI and Power Pivot during his 13-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, P3 Adaptive has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.
Comments (11)
-
As I wrote in my blog (see below), this technique is completely unsupported by Microsoft and might break your Excel file (I don’t have more information to describe in which conditions this might happen).
Just to warn some future BI developer regarding that! -
Sharepoint websites doesnot support vba enabled workbooks, does powerpivot replaces the vba??
-
Hi.
This is an old entry. However, I’m trying to execute the Refresh Sub in te code and I get the message 438 run time error, the object don’t support the property or method. This happen in the line
ActiveWorkbook.Model.Initialize
I’m usig Excel 2010.
Can anybody help me?
Thanks in advance.
-
This may work for 2013 as it is simply just one line of code. But I have been trying to implement this into a 2010 application and it isn’t working.
-
I’ve experienced a “corrupted” data model with this macro unfortunately.
Simply returning wrong numbers (via pivot and cubefunctions). Without any warning.
Analyzing…. only when I rebuild the measure (with a different name) it started showing crazy error message, giving the signal that sth with the datamodel was wrong.
Sad.
BTW: This was a workbook that originally had linked Excel-tables and Power Query pulls in it – both wouldn’t work with this macro so I’ve deleted/replaced them. Maybe this was the cause of the corruption. -
Is still unsupported?
I’m using ActiveWorkbook.Model.Refresh in Excel 2013 and seems to work great.
-
If i add only that sentence in my macro it will refresh all of my pivot tables and data, that is coming from powerpivot? i’m using excel 2013
-
If you have any Pivots with a Power Pivot Data Model then using the “ActiveWorkbook.RefreshAll” will first refresh the data Model.
-
We have a much better and simpler solution than the one Tom Gleeson describes https://ukanexcel.wordpress.com/2016/11/30/refreshing-power-pivot-in-excel-2010/
-
I know this is an old thread, but I came across this in my search 🙂
VBA methods on Data Model is now documented here: https://docs.microsoft.com/en-us/office/vba/api/excel.model.refreshI have a workbook with a lot of data being loaded via PowerQuery (now Get & Transform) & with relationships and measures in the Data Model, and needed to create a button for a co-worker to refresh just one table. I first looped through the tables in the data model (per the commented out version) l to get their names and then called “refresh” on the one I wanted:
Sub Button1_Click()
‘ For Each t In ThisWorkbook.Model.ModelTables
‘ Debug.Print t.Name
‘ Next tThisWorkbook.Model.ModelTables(“My_Table”).Refresh
End Sub
-
Just successfully tried ThisWorkbook.Model.ModelTables(“My_Table”).Refresh. Thanks for sharing!
Leave a reply
Your email address will not be published.