Determining Whether An Excel Workbook Contains PowerPivot Data

Posted on by Dick Kusleika

By David Hager

One of the issues with the 1st version of PowerPivot for Excel is that the add-in does not expose a programmatic interface with Excel. Thus, the data in the PowerPivot window cannot be manipulated through VBA or VSTO. This creates a problem in that there is no straightforward way to identify whether an Excel workbook contains data that has been pulled into the workbook by PowerPivot. Of course, this can be done manually by opening the PowerPivot window, but that method might be tedious if you had several hundred files to check.

Initially, I wanted to create a workaround to this problem by exploiting the method that PowerPivot stores data in an Excel workbook. Since that data is represented as an XML part in Excel file, one way to solve this problem would be to programmatically unzip the Excel file and then identify that one of the parts is a folder/file with a .data extension. I believe that by using EditOpenXML and Windows FSO, a method to identify the presence of the .data file in an Excel workbook could be crafted. However, another idea came to me and I abandoned this approach.

I realized that the CUBE functions which communicate with the PowerPivot data would behave differently in Excel workbooks with and without that data. So, I created a generic CUBEVALUE formula that looks at “nothing”.

=CUBEVALUE("PowerPivot Data",)

I discovered that this formula generates an error (obviously) when entered in a worksheet cell, but the errors are different depending on whether the workbook contains PowerPivot data or not. If the workbook has PowerPivot data, the error is #N/A. If the workbook does not have PowerPivot data, the error is #NAME?. Well, I thought that the solution was easy. I wrote this line of code and tested it.

Sure enough, the code worked as I expected (sort of). An error was created when the code was run and the error was different for the two workbook types (Error 2042 for a PP workbook and Error 2029 for a non-PP workbook). However, an unexpected problem occurred. The line did not throw an error when run; it merely stored the error in the variant variable. Thus, normal error trapping could not be used. After doing some research, I found a Microsoft KB article which identified this problem. Unfortunately, the workaround was to place the formula in a worksheet cell and read out the error value from there. That is not what I wanted to do. Finally I ended up solving the problem by converting the error message to a string and extracting the numeric error code from it.

This worked great, and I was able to come with this simple function for identifying a PowerPivot workbook.

In the testing of this function, I found that I had forgotten that early PowerPivot workbooks named their data source “Sandbox”. So, a slight modification to account for this produced the final version.

I hope that this technique adds another useful item to your PowerPivot toolbox.

Editors note: Code samples edited from original article.

15 thoughts on “Determining Whether An Excel Workbook Contains PowerPivot Data”

  1. You can use CVERR instead of parsing the string

    Dim var As Variant
        var = Application.Evaluate(“=CUBEVALUE(““PowerPivot Data”“,)”)
        If var = CVErr(xlErrName) Then
            MsgBox “No Powerpivot”
        ElseIf var = CVErr(xlErrNA) Then
            MsgBox “Powerpivot”
        End If

  2. I was looking for information to do exactly that. Thanks!
    Do you know of a list of Excel errors online?

  3. @David Hager,

    The CVError constants (one of which Charles Williams made use of) can be found by going into the VB editor and typing…

    cell error values

    into the search field at the top of the window. The first item returned should be labeled “Cell Error Values”… click it to see the help files for them. If you type…

    trappable errors

    into the search field instead, the first item returned should be labeled “Trappable Errors (Visual Basic for Applications)” and click it will that you a help file showing you all the trappable VBA error codes.

  4. Thanks, Rick. I am learning 10000 new things about Excel (and PowerPivot) and it may be pushing 10000 things I already knew out to make room.

  5. I have found a caveat to the use of this function. Apparently, if a workbook contains or did contain a table used as a data source for a PowerPivot workbook or contains a linked table to a PP workbook, this function will tag it as an PP workbook. It looks like PP creates a cube in any workbook associated with the creation of a PP workbook (albeit an empty cube in the workbooks described above). That is interesting information, but it does compromise the use of this function to some extent.

  6. Hi David,

    I too had this issue one month before. In 1st version of powerpivot in excel,add-in does not expose a programmatic interface with Excel.I was searching for the solution but after seeing this post only i solved my problem.So this is a very useful article by David. many thanks to him. My only suggestion is please keep on posting this type of blogs more and more. Thanks for your great guide.

    http://godwinsblog.cdtech.in/2010/12/microsoft-office-excel-add-in-plug-in.html

  8. David,

    I thought about this problem too, but I thought of looking for the connection, like so

    Dim cnn As Object

    On Error Resume Next
    Set cnn = ThisWorkbook.Connections(“PowerPivot Data”)
    On Error GoTo 0

    MsgBox Not cnn Is Nothing

    I noticed that the connection’s CommandText is still Sandbox, even in the latest version

  9. Bob, thanks for your post. After some work, I was able to modify your code to make a function that really does indicate whether a workbook contains PowerPivot data. It eliminates those workbooks that have, for one reason or another, an empty OLAP cube embedded in the workbook.

    Function WbHasPowerPivotData() As Boolean

    Dim wc As Object
    Dim wc2 As WorkbookConnection

    WbHasPowerPivotData = False

    On Error Resume Next
    Set wc = ActiveWorkbook.Connections(“PowerPivot Data”)
    On Error GoTo 0

    If Not wc Is Nothing Then
    Set wc2 = ActiveWorkbook.Connections(“PowerPivot Data”)
    WbHasPowerPivotData = wc2.OLEDBConnection.IsConnected
    End If

    End Function

  11. Ok, I’m stumped. The function in comment #9 works perfectly if the the workbook to be checked is openly manually. However, if the workbook is opened programatically, the function does not work (return the correct value). I have read that in Excel 2010 that there are some problems with the Workbooks.Open method, but I am not sure if this issue is related to my specific problem. Thoughts?

  12. Hi,
    Actually, the power pivot definitions and al’ are also stored in several of the customXmlParts of a book.
    Sub test()
    Dim x As CustomXMLPart
    For Each x In ThisWorkbook.CustomXMLParts
    Debug.Print “######## ” & x.NamespaceURI; x.XML
    Next
    End Sub

    So, how about leveraging these properties.

    Function HasPowerPivot(Wbk as workbook)
    On Error Resume Next
    HasPowerPivot = Not (Wbk.CustomXMLParts(“http://gemini/workbookcustomization/MetadataRecoveryInformation”) _
    Is Nothing)
    End Function

    However, I did notice that, if you remove the pp Connection, the Gemini CustomXmlParts stays there until you save the workbook.

    Sebastien

  13. Sebastien, excellent!

    I’ll incorporate this into my automated routine and see if it allows me to identify PP workbooks on the fly.

    Let you know.

  15. Yes it will remain but until the book is saved, i believe.
    There is definitely a lot of info there; i spent hours looking into those. Eg: the MetadataRecoveryInformation part has a DataSources tag with all datasources in a separate DataSource tag.
    I did also play with the .data file and tried to corrupt the binary part with vba to force a reload/requery of the data, but instead a warning popped up and the entire data was removed without a chance for the user to reload it.

    sebastien

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.