Automating PowerPivot Refresh operation from VBA – The Code

Download the example workbook from here … UPDATE: New version of code available here … (This ‘trick’ will not work in Excel 2013, that’s the bad news, the good news is it’s no longer needed as PowerPivot (in 2013 known as the Data Model) is now fully supported directly from the Excel Object Model. I’ve added some code to check if 2013+, if so, use this new functionality).

I tried it and it just works also in PowerPivot v1!
It would be useful to recognize when the refresh in PowerPivot is completed so that you can refresh other objects in Excel depending on PowerPivot data.
Good job!

Hi Marco, I am new to this site and I just tried the script to refresh PowerPivot v1 and I am getting Error 1004 – Application or Object Defined Error. I can refresh the powerpivot table manually. Can you please help.

To refresh a single table you have to send a Process command specifying a dimension – it becomes the process of a dimension, after all. The only issue is to retrieve the dimension name, which is the name of the table followed by a GUID. You can retrieve these commands by generating the processing script from SSMS in Denali. Here is an example (I’m not sure, but it might be working on PowerPivot v1, too):

Now that I can separately refresh individual tables, one option would be to specify (or deduce) the tables to be refreshed; enclose in a Parallel Tag to take advantage of parallel loading (assuming local cube AS engine can take advantage of this?); finishing off the refresh with a “metadata table” outside the Parallel Tag (to ensure it runs in sequence after “business data” loads). This “metadata table” could provide the end user with useful stats and visual confirmation of a successful update and could also be used by VBA code to automate such checks (traditional DW star-schemas often used junk dimensions or Data Quality dimensions to provide similar service).

Makes it even easier then; specify the tables to be refreshed in sequence, check the DMV last update of the last table’s “dimension” (assuming last_update is only updated after, not during or before, refresh is complete).

When I added a large data set to my test model to check out what DMVs would work best for identifying a completed refresh (discover_sessions and discover_commands were the hot favourites) I discovered that the ADO call blocks while refreshing. So whatever I saw when testing (late at night) with my initial small sample, the process command isn’t asynchronous. Maybe a small 1 or 2 second delay to allow proper re-sync between Excel and the AS engine is all that’s required.

Does bring up another problem with the connection timing out for very large datasets (defaults to 30 seconds I think, and can’t be changed once the connection is established) but I can get around that by using an ADODB.Command object (which has its own TimeOut property) instead of an execute call directly against the default connection.

As I wrote in my blog, 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!

As Marco pointed out on his blog http://bit.ly/pp0UXz, a corrupted workbook could be very expensive (but then, PowerPivot itself has been known to do that without the help of any VBA hacks), but then again it could also be very cheap, such being the nature of spreadsheets.

Backups, more backups and still more backups is the solution (and so easy to do in VBA).

If people did end up corrupting and phoning the Excel help desk in mass complaint, then perhaps MS would get the message that the current refresh process is sub-optimal. I would actually be happy with a pure ribbon approach i.e. a button for “Update External PP Tables & Refresh Pivots”, “Update Linked Tables & Refresh Pivots”, “Update All Tables & Refresh Pivots”.

Unlike the DLL approach , this method is documented (if not supported) in that the same steps could be used to update a normal SSAS database. However, in the case of PowerPivot there’s obviously some sort of com automation feedback into Excel (as I found out when I attempted to refresh from within a .NET add-in in UDF mode – automation calls are not allowed from UDFs). There’s obviously something been written back to the workbook which, if the refresh fails (an ADO timeout on a very large dataset would be an obvious fail-point), might result in corruption.

If only the embedded database ends up corrupted, that shouldn’t be a problem as the meta data held in the workbook’s CustomXML parts seems to work well as a recovery method. But if the CustomXML parts themselves also end up zapped, then a return to previous backup would be the only option (unless a bit of XML hackery is something you enjoy).

If anybody out there comes across any such problems please report them to this blog post or email or twitter me,

I agree with you – I asked to Microsoft whether this approach would safe enough and they pointed me out that they don’t have a full test of this scenario and for this reason it is unsupported. My speculation is that an issue can happen in particular condition (i.e. an error in XMLA?) but this is the reason I wanted to highlight the potential issue.
That said, this is better than nothing and I just warned people about doing their backup – it’s easy, after all, just copy a file .)

… activates the connection (manually fetching something from the cube will do likewise). It this doesn’t happen, then the connection will not exist and you’ll get that error. I can simulate the error by commenting out the Refresh line (before I touch any PP pivot tables).

If you’re getting the error after the a successful refresh that would be worrying. Try it after you’ve played with a PP pivot for a bit.

The fact that this is the only way to get a handle on the connection is a worry, as MS could easily nobble it, by closing the connection between refreshes (as would happen with a normal data connection linked to say an Excel Table).

I’m afraid I DO run the refresh from your code before this fails… I will try recalcing a CUBE Formula as well. I am using Win 7, Office without SAP1, PP CTP3 Denali with SQL Server 2008 R2.
I’ll let you know if that works for me.
Thanx again
Dick

OK – I created a Pivot Table from the PP data and I refreshed that pivot table inside my code and THEN the ADOConnection worked. Now let’s see if the rest works for me 😉 .. I’m sure that this is going to be a major asset for me. I’ll let you know if it works and if it does I’ll owe you forever ;-).

Thanks very much Gab. This is the first time I am visiting this site and I just followed the steps, could refresh the powerpivot without any issues for one table (assigned tablename to lTable). How do i loop through all the tables? Thanks again.

No, MS Access works just fine, only table type you can’t refresh are linked (i.e. same workbook) Excel tables. I’m guessing you’ve modified the code? Send it on to me, with the workbook if possible, and I’ll take a look at it. (send to tom@gobansaor.com

I’m using PP v1 with refresh working in one report, whereas in another I get the runtime error -2147467259 as mentioned by Dick. I have no linked tables, only tables and queries from/against SQL Server.

Hi Tom,
I’m not sure where to look for version – timestamps on ppRefresh.zip content appears to be from 9. sep. 18.13. I haven’t modified the code – just exported the PPRefresh module and imported it in my Excel PP v1 file and hooked it up to a button in the ribbon. There seems to be some interference between reports as the first report which worked just before now fails with an error referencing the file name of the second report. Going into debug, it fails at comm.Execute in doXMLA. Can there be any caching across files/sessions even with only one file open at a time ?

Forget last reply, you do seem to be using latest version. The major change in that version was to handle the situation when multiple PP cubes where open in the same Excel session. Is that what’s happening to you?

Hi Tom,
sorry for disturbing the force 🙂 Found out that the refresh failed due to timeout. I have the refresh code hooked up to a custom button on in a custom group on the ribbon and got confused by a seemingly application-wide macro reference that continues to launch the last report in which I have hooked this up when I refresh a report in which I have hooked this up earlier on even if I have imported the code in both. After extending the timeout, it works, and so now I’m just looking for a way reference the “Refresh” macro in the local file, in stead of within the file in which the button was hooked up on the ribbon.

I (like many others) wanted to try your code.The error I am getting is in the xmla execute and it says: . PivotTable1 is a Pivot table i created based on the PowerPivot Data. Any ideas for me lease? Thanks,Nala

Sorry, the error was left out when posting: Run-time error ‘-2147467259 (80004005)’: Either the dimension with the ID of ‘PivotTable1’ does not exist in the database with the ID of ‘xxxx..’, or the user does not have permissions to access the object. Thanks!

Looks like you’re passing in the Pivot Table name as the table to refresh. This variable can be set to a source PP model table for those cases where you wish to only refresh a single source table. Normally you leave this blank to refresh every source table within the model.

I see that the cnn is set to: l Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Subqueries=2;Optimize Response=3;Cell Error Mode=TextValue”. Is this OK?

Thank you Tom, setting the timeout to zero yield the same result: Run-time error ‘-2147467259 (80004005)’: The operation has been cancelled. Any other ideeas for me to try? I really appreciate your quick response 🙂

Thank you Tom. The file I need the refresh to work on has an embeded power pivot. I tried the code on a clean file with a small (100 rows) PP table collected from a SQL server database. It worked. So at least we rulled out the refresh code as a problem 🙂 The embeded datasource is about 100K rows. I have no control about it, any idea of what can be wrong with it? It is built from the same server as the table I tried on. I did not design the embeded data source but I have to work with it… And yes, the refresh works manually on the embeded PP file. I work on a Windows 7 professional machine, Office 2010, VBA 7.0.1625.

Error -2147467259 (80004005)’ is an ADO error which when raised by the Refresh code can be caused by a number of issues.

Most likely either:

(a) No PowerPivot backed PivotTable created in Workbook (hence no connection to tap into) – solution 1st create a PivotTable based on the PP model.

Or (b) Authentication problems with accessing source data – solution make sure you have access and that all credentials are stored correctly within the connection i.e. prompts for username/passwords are not handled.

The -2147467259 error is a generic VB runtime error, it can be caused by almost anything. The number in brackets afterwards (and any text) is the important bit. Please provide as much information as possible (version of PP, type of data connection, size of data source, whether it works manually, whether you’ve initially “practised” using the example workbook and it worked in your environment; have you changed the code, if so what etc.).

Also be ready to send me both your workbook and data source (the original or, if issues of confidentially, a similar dataset that also causes the same problem).

The code has been downloaded 1000+ times, has been used successfully by at least 50 of those to my knowledge (some people actually email me to say thanks and share their successfully experience – imagine that), so it does work, stick with it, think of it as a learning experience, not a handout.

And please do not pepper the site with the same comment on multiple blog posts, if you do you’re likely to trigger the auto-spam logic and/or my indifference.

Oh, it’s also polite to provide a name so we can have a proper conversation.

Dear Tom,
Thank you for your answer,
First of all : sorry for my laconic post .

I know that your code works – i use it to update another pp table, i just have problems with this one

Here are details :

I have excel 2010 32 bit + PowerPivot V2 (polish versions)
The error is :-2147467259 (80004005)’ , The message could be translated (from polish) as something like that :
” The operation has been cancelled beacuse execution of other operation in the transaction has failed” – don’t know the exact english text 😦

I try to update a single table , beacuse when i update all tables(without specyfing the name of the table in the code) i recive some memory error (message about considering switching my excel to 64bit or adding some RAM)

As i’ve mentioned above – the code works with other table (same workbook)

If i try tu refresh manually all tables i also receive memory error, if i refresh single table manually it works.

Sources for both tables are excel files – the working one is 15MB file, the other is 45MB
The 45MB file has about 160k rows, but i try to import about 60k rows (by column filters in PP window ->table properties)

I start to think it can be a memory issue, but it’s strange that it works in manual mode and doesn’t work when using code.

It’s a memory issue pure and simple; internally the add-in likely uses a different approach to refreshing and undoubtedly manages memory aggressively. PowerPivot needs all the memory you can throw at it and ideally a 64bit machine.

Another way to get the -2147467259 error is to forget to change the Data Source for the CSV files to their location on my machine (as per the instructions). This looks great. Should suit my needs for update well.

Thanks for this great code! i did find a small bug. On the line, xmla = Replace(xmla, “<<>>”, databaseID), there is not a “<<>>” referenced in the above XMLA so there is nothing to replace. Same with <<>>

Thanks for posting the code! I seem to be having trouble with the refresh freezing on PowerPivot tables that are sourced from Excel files located on a network share and a SharePoint document library. Is the code designed to handle those types of data sources or will I need to modify it?

Source of the data shouldn’t matter (check 1st that they work manually) all sorts of datasources have been processed by the code. The only exception are “linked” tables in same workbook as PP, these do not work.

so, reading through the code, you open an excel workbook and will that refresh any powerpivot workbok? does the powerpivot workbook need to be opened.
i am trying to come up with a solution for a powerpivot workbook that is sourced via a sql table. i do not want to open the powerpivot work book at all and handle inside of code. powershell or c#. does this code give the abiltity to do that task and/or help with the code to potentially do so.

Excel needs to be loaded and the workbook containing the model to be refreshed must also be loaded.

In this version of PP (but not 2013) would be possible to refresh the model directly in SQL Server, export and then insert via the XML SDK into one or more workbooks without opening the said workbooks in Excel (so could be done on an Excel-free server).

thanks,
so, i could perform my tasks without technically opening excel at all. using the openformat or other techniques to get at the core workbook.

i guess this is why people put pp up on sharepoint and then put a refresh on sharepoint. then with ssas 2012 and the tabular format this would go away as well and the excel pivot tables i currently have hitting an excel powerpivot workbook will just sit on the ssas 2012 tabular data format and work like any other odbc connection.

Yes, you could say, develop he model in Excel, then import the model into SSAS Tabular and then either modify the original workbook to now look at the server(via openformat manipulation) or, as you saidm just create a new set of workbooks using pivots based on SSAS ADO connection. This works better in Excel 2013 than 2010 as the local and server-side pivot control dialog have been unified in Excel 2013.