Updating PowerPivot using VBA

[ellenl5974]

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

 

[powerpivotpro]

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/

 

[ellenl5974]

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

 

[ellenl5974]

BTW, great blog! I have been haunting it for a while. Brilliant insight.

 

[powerpivotpro]

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