THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Workaround for lack of PowerPivot API in Excel

A few days ago a post about VSTO-based PowerPivot Workbook in the PowerPivot Team Blog shown a workaround for the lack of PowerPivot API in Excel. In reality, I don’t like this workaround at all, because it relies on displaying message boxes to the end user in order to click “Update” button in the PowerPivot Ribbon. I understand the reasons why Microsoft probably is not publishing such API: doing that, they should support it for many years and probably they want the freedom to change it in the upcoming releases. I don’t know whether they already did it between v1 (2008 R2) and v2 (Denali), but my speculation is that a future version of Excel might integrate PowerPivot in a stronger way and the logical consequence is that its API would become part of the Excel object model. As I said, this is only my hypothesis and I don’t have any data to support it.

That said, the problem is that the lack of a PowerPivot API is really a missed opportunity today. There are companies, like Predixion, that take the risk of accessing to an undocumented and unsupported API to run their solutions. They generate PowerPivot workbooks from scratch with their Add-In, using PowerPivot compression to ease the data transfer from customer to their cloud infrastructure. They know they might have to rewrite part of their add-in for every new version of PowerPivot, and in case of any issue, they cannot rely on Microsoft support. It is a very expensive choice that cannot be justified in a large number of scenarios.

Between these two extremes, I would like a simple workaround to simply automate the PowerPivot Refresh operation from VBA/VSTO. Even in an unsupported way. Do you have the same hope?

Published Monday, August 8, 2011 5:22 PM by Marco Russo (SQLBI)
Filed under: ,



Jeff said:

Yes please!

August 8, 2011 11:27 AM

Hrvoje Piasevoli said:

Hi Marco, yes absolutely a missed oportunity that could have solved a lot of scenarios. Just today I have finished a concept of feeding the PP model from cube report cell - reading the server custom drill through action which by the way is easily constructable using the PivotTable object model. The only missing part is the refresh tables piece. But at least it allows me to use the multimillion molap dimension attributes (through the fact table) in the already prepared dashboards based on the power pivot model. It would be perfect if I could just update the PP table source query using this macro instead of teaching the users to either update PP with the linked drill through table or copy paste the drill through mdx in the query editor.

And if there was a way to union the results of two or more drillthroughs or incrementally add source rows I'd even solve the multiselect problem easily. Calculated cells and utility dimensions are not a problem when you go the macro path.

Best regards

August 8, 2011 1:10 PM

Marco Russo (SQLBI) said:

Thanks for the feedback Hrvoje.

Just a question: do you create a drillthrough MDX query on the fly and fill an Excel table that you use as a Linked Table in PP? Just to understand how your solution works.


August 8, 2011 1:22 PM

Eugene Asahara said:

I would go wild with the capability to intergrate into PowerPivot.

August 8, 2011 2:19 PM

m-b said:

I have the same hope as well. I've read somewhere though that in v2 linked tables are automatically updated when the Excel data changes? If that's the case you could update the Excel data using VBA and then the PowerPivot data would be refreshed as well. That's what I tried as a workaround in v1 but it didn't work, would be great if it does in v2 (haven't been able to test it yet).

A question though; doesn't having your data in both Excel and PowerPivot 'double' your data in de workbook?

August 8, 2011 3:33 PM

Marco Russo (SQLBI) said:

I haven't seen any difference between v1 and v2 regarding linked tables refresh. Where did you read that?


August 9, 2011 11:26 AM

m-b said:

I read it in a comment on PowerPivotPro:

Shame if it isn't true...

August 9, 2011 12:04 PM

Marco Russo (SQLBI) said:

I read it, but the Automatic feature was *already* on v1.

Now we can discuss what "Automatic" should mean reagarding to Linked Table updates, but the v1 and v2 behavior are the same to this regard.


August 9, 2011 12:15 PM

m-b said:

Ah ok. If I remember correctly the 'Automatic' feature only updates the linked table when you manually activate the PowerPivot window. In my opinion it should mean that when the Excel data changes the PowerPivot data will instantly change as well. If that was the case you could use VBA to pull the data into Excel (which is linked to PowerPivot) and then refresh the PowerPivot data source and the pivot tables. That way you could sort of automate PowerPivot but you would be limited to the 1 million row limit of 'regular' Excel...

August 9, 2011 1:11 PM

Marco Russo (SQLBI) said:

I know but this (waiting for PowerPivot window activation) is an expected behavior. Consider that every refresh requires the whole table to be read and moved to a PowerPivot table. If this would happen for every single cell change, it would be very slow. There could be a better way to handle that in an automatic way (i.e. waiting for Idle and then refresh PowerPivot) but that's the actual implementation today.


August 9, 2011 1:17 PM

m-b said:

Thanks for the clarification, I guess it makes sense in the current implementation. Shame there isn't some sort of workaround but I reckon Microsoft rather wants you to invest in SharePoint then enabling you to automate PowerPivot in other ways...

Which reminds me; is there a way you can update the PowerPivot data based on parameters within cells in Excel? That's another thing I'd love to see a workaround for.

August 9, 2011 1:31 PM

p vc said:

Marco - since we (you and I and these other folks) are only looking for the refresh functionality, can we not use the same undocumented API that Predixion is using?  Really just one function call is all we need for now - such a simple thing.  I would absolutely be willing to use an undocumented and unsupported function - if it changes, then so be it, but in the meantime it will certainly help me automate something that is just a big annoyance for my customers - and frankly quite embarrassing as it requires numerous manual steps that harken back to another century.

Does anyone out there know the dll/api/ whatever predixion with their intimate connections to MS used?  Surely if an unaffiliated company can get this info then so can some of the MS loyal customers or other ex-employees (like the Predixion folks).  Anyone?

August 10, 2011 2:23 PM

Marco Russo (SQLBI) said:

Most of the PowerPivot user interface code is written in .NET. The internal engine is a C++ DLL and the communication between these two layers is handled by using OLEDB for OLAP connection, I believe. Thus, there should be at least two ways to implement the refresh:

1) open the OLEDB connection with the internale engine (SSAS) and request a process operation by sending a XMLA statement

2) call internal method of the PowerPivot DLLs by leveraging on Reflection in .NET

Even if the latter might seem better, the former is probably more robust.

I just don't have other time to spend on this! :)


August 11, 2011 11:39 AM

p vc said:

Thanks for pointing me in the right direction Marco!

Hmmmm.... that sounds pretty complicated :)  However I will poke around and see if I can get anything to work.  If I do I will post the how-to, and reply to you here with a link.

In the meantime, if anybody on the Microsoft team feels like posting unofficial how-to instructions somewhere then I would be eternally grateful! :)

can't wait for the official API to appear (eventually).

August 11, 2011 1:35 PM

Ron said:

Hi Marco,

I did some reflection. The only clear refresh-related methods I found was in a namespace called Microsoft.AnalysisServices.Modeler.DataRefreshWizard.

There is a class called DataRefreshProgressObject which has 2 methods: StartDataRefresh() and StartDataRefreshAll().


August 12, 2011 4:17 PM

Hrvoje Piasevoli said:

Hi Marco, sorry for the late response, I missed your question... "do you create a drillthrough MDX query on the fly and fill an Excel table that you use as a Linked Table in PP?"

I am supporting 2 scenarios, first being customized drill through and reusing the same result table. The second is copying the DT mdx in clipboard for the user to paste in the source query inside power pivot to overcome the 1M limitation and duplicate data. It's still not quite satisfying, and I'll have a look at what can be done using XMLA thanks for the hints.

Thanks to all for the useful and interesting discussion.

August 13, 2011 4:05 AM

Marco Russo (SQLBI) said:

Thank you Hrvoje!

August 13, 2011 6:04 AM

Marco Russo (SQLBI) said:


to refresh table probably you should:

1) Get a connection to PowerPivot sandbox engine

2) Get the list of tables (look at the Tables property in the Microsoft.AnalysisServices.Modeler.Storage.DataModelingSandbox class)

3) Call the Process method on each table

Otherwise, you should use XMLA connecting directly to the sandboxed engine.


August 13, 2011 10:13 AM

Ron said:


for the DataModelingSandboxConnection I need a connectionStringInput. I've tried several options like:

string connstring = @"PROVIDER=MSOLAP;DATA SOURCE=C:\inetpub\wwwroot\test.xlsx";

string connstring = @"PROVIDER=MSOLAP;DATA SOURCE=http://localhost/test.xlsx";

With the datasource pointing at the file location I receive a {"The connection string is not valid."} error. When putting the xlsx on a webserver I receive a {"The remote server returned an error: (404) Not Found."} error, although when debugging the error I see the connectionstring is extended with some extra info. Under the hood the following is created:

Provider=MSOLAP;Data Source=http://localhost/test.xlsx;Locale Identifier=1033;SQLQueryMode=DataKeys

Any ideas?


August 13, 2011 4:06 PM

Marco Russo (SQLBI) said:

You have to call the API in-process with Excel.

Take a look at the embedded connection used by PowerPivot in any workbook:

Provider=MSOLAP.5;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue

August 13, 2011 5:38 PM

Ron said:

I saw that connection string in the connections in Excel.  

I will see if we can use VSTO, but I guess that will be in a few weeks after my holiday (to Italy :))

If somebody else can find some time to take it from herer. I used Red-Gate reflector for examining the APIs.


August 14, 2011 1:33 PM

Marco Russo (SQLBI) said:

Have a nice time in Italy and, if you have time, don't miss Torino!

August 15, 2011 5:22 AM

Steve Bartalos said:

Check out PowerPlanner (, a New Planning, Budgeting, Forecasting tool for PowerPivot.

It supports Writeback, Top-down planning, Goal-seeking, and Row-level security for PowerPivot.

September 15, 2011 4:13 PM

Rishi said:

Hi Marco,

Any idea how I can use VBA (Excel 2010) to update the powerpivot data connection location/file path please?

December 5, 2013 12:32 PM

Marco Russo (SQLBI) said:

December 6, 2013 5:38 AM

Navin Kumar said:

Hi Guys does any one have any script to do the refresh action in power pivort.

April 1, 2014 8:54 AM

Marco Russo (SQLBI) said:

Navin, follow the link in my previous comment.

April 1, 2014 2:23 PM

Todd Chittenden said:


The link from your Dec 6 reply id broken. Any updates?

I, too, would like to see Microsoft enable VBA access to PowerPivot.

August 14, 2014 3:30 PM

Marco Russo (SQLBI) said:

I hope it's a temporary issue. Another blog is here (, but newest post are in the previous URL.

No news about VBA access to PowerPivot, we have to wait the next major Office release, probably.

August 14, 2014 7:29 PM

Witold said:

Dear All, Dear Marco,

i am looking for a vba code that will determine if a closed .xls file has a powerpivot model (excel 2010). Any ideas?

January 24, 2015 11:21 AM

Marco Russo (SQLBI) said:

I don't know in VBA - in .NET I would take a look at the Open XML SDK:

January 24, 2015 11:59 AM

Avinash Mishra said:

Dear Marco ,

I need to Change Connection String in Bulk Excel 2013 files using C# code.I need to ask , whether its possible for Excel 2013 power pivot. Because , same code is working fine with Excel 2010 and creating exception for Excel 2013 files.

Any ideas regarding this ?

January 27, 2015 3:59 AM

Marco Russo (SQLBI) said:

Sorry, never tried, I should investigate (but no time now)

January 27, 2015 4:17 AM

Avinash Mishra said:

Thanks for feedback sir.

If you get anything for same , please let us on this link ...

or ;

Trying to implement this since months with no success :(

Any help will be great ...

Thanks in Advance

January 27, 2015 7:53 AM

Marco Russo (SQLBI) said:

Sorry, too busy time these days...

January 27, 2015 8:04 AM

des77 said:

I know this is old but if anybody needs a solution my experience is a follows.  This can be done with share point enterprise 2013.  I have actually used it as a stop gap until our tabular instances are up and running.  If you use excel services to auto refresh PP data in a PP library once you have created a PP model in an excel workbook you then have an option of creating another workbook based on the workbook with the power pivot model embedded.  Multiple work books in this way can point to the same power pivot model.  The caveat of course is that you need an on-premise (only on-premise as I understand it) share point implementation.  Also there are limits, my practical experience is when the excel workbook as a whole starts exceeding 150 MB in size it starts to become slow and unwieldy sometimes with unusual errors being thrown/crashes.  

February 15, 2018 3:23 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement