Updating PowerPivot using VBA

ellenl5974

New Member
Joined
Jan 30, 2012
Messages
5
I have been told that PowerPivot is not meant to be used with stand-alone spreadsheets. So, I completely rebuilt my workbook to use only Pivot Tables with multiple vlookups.

However, after much time trying to find a workaround for the Income Statement. The only solution I have come up with is to use a PowerPivot table with a custom measure. It looks great! However, it does not dynamically update. When I go to deploy to the users, I am dreading having to tell them to open the PowerPivot window, hit refresh, then go to Pivot table and hit refresh.

Any other solutions? I would love to program a VBA buttton to update for them!

Thanks,
Ellen
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi Ellen! First of all, I'd like to know what you mean by "PowerPivot is not meant to be used with stand-alone spreadsheets" - that sounds like it might not be right :)

Secondly, there is no official support for VBA-driven refresh of PowerPivot data. However, an enterprising PowerPivot pro named Tom Gleeson seems to have cracked an "unsupported" workaround.

I linked to it from my blog:

http://www.powerpivotpro.com/2011/09/powerpivot-refresh-thru-vba-at-last/
 
Upvote 0
Thanks for the reply. For the back story, I built the entire file using PowerPivot exclusively: loved the ability to define relationships, much more database functionality. But then, I came across the refresh problem. While searching for a solution, I came across a few articles that mentioned that PowerPivot is really meant to be used with SharePoint files, etc. I was just employing it within my own sheet to increase the database functionality. That's when I started redesigning the entire sheet to be solely Pivot Table.

Thanks for the link to the solution. It may be a bit beyond my abilities. I may just have to settle for writing extensive instructions to go with my workbook.

Thanks!
Ellen
 
Upvote 0
Make no mistake: I am a HUGE fan of using PowerPivot with SharePoint. It opens a lot of new doors.

But those benefits are all purely NEW benefits. They are not things you get with normal Excel, either.

So with the exception of the "no VBA refresh" issue we're covering here, and perhaps the requirement of XL2010 + the addin installed, I can think of no downsides of using PowerPivot vs. regular pivots. It has been all pure "win" for me, even on the desktop, without SharePoint.

So I'd urge you to use PowerPivot whenever it seems like it will help you - you shouldn't have to spend any more time in VLOOKUP-land :)

And thanks for the kind words about the blog :)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top