More on Multiple PowerPivot Versions

The previous blog post, Supporting Multiple PowerPivot Versions, contained a PowerShell script that has proven to be very helpful for us on the product team. We get a fair number of workbooks from a variety of sources created with many different versions of PowerPivot, usually with a request for help with some problem that happened when trying to use the workbook. One important piece of data that doesn't always come with the request is the version of PowerPivot that last saved the workbook. Sometimes, we even need this information for test workbooks we've created for our testing. Since we get new builds multiple times a week, the complete version number is needed to determine whether an issue we're having with a workbook is already fixed or not. For all those reasons, the script has proven to be a frequently used tool for us.

However, we noticed that the script didn’t return the correct version number when it was run against a workbook created with PowerPivot in beta versions of Office 2013. This is because the location of the version number in the custom XML data that PowerPivot saves inside the workbook file has changed. In PowerPivot for Excel 2013 the version number is saved in this location:

gemini/pivotcustomization/PowerPivotVersion

While PowerPivot for Excel 2010, saves the version number in this location:

gemini/workbookcustomization/PowerPivotVersion

Although this change breaks the initial version of the GetPowerPivotVersion script, the difference allows us to update the script to provide additional workbook information, such as the version of Office that was used to last save the document. The updated version of the script is provided at the end of this post.

To demonstrate, here is a screenshot of what you get when running the original script against a workbook saved with PowerPivot in Office 2013 customer preview:

Note that the default value of “00.0.0000.00” is returned rather than the actual version of PowerPivot that created the workbook.

Here is a screenshot of what you get when running the updated script against the same workbook:

In addition to returning the correct version, the script also displays a more user-friendly message that includes the Office version. An additional minor modification to the script was to wrap the $filename variable in the workbook Open() call with the PowerShell Resolve-Path command which allows relative paths to be passed to the script.

Another thing to notice about the version number being returned from Office 2013 PowerPivot: it’s in a different format from the version returned from Office 2010 PowerPivot. In PowerPivot for Office 2010, the version number looks like this:

11.0.2100.60

PowerPivot for Office 2013, on the other hand, displays the version in this format:

2011.110.2809.6

The last two parts of the version number (“2100.60”; “2809.6” above) indicate the release build number. This lets you know if the workbook was saved with a released or pre-released build of PowerPivot. Here are version numbers for the various public releases of PowerPivot:

One more point about Office 2013 PowerPivot: Office 2013 has the ability to add PowerPivot models with basic features into a workbook without invoking the PowerPivot add-in (You can find more information on this in the previous Analysis Services blog post Going All In with Excel 2013). Workbooks containing these types of models that were never modified by the PowerPivot add-in will not be detected by the script.

Following is the updated script. We hope you will find this update helpful.

# GetPowerPivotVersion.ps1
Param($filename)
if (!$filename)
{
  "Usage: GetPowerPivotVersion.ps1 <workbook file>`n"
  return
}
 
$ppVersion = "The workbook doesn't contain a model saved by PowerPivot"
try
{
  $xlApp = New-Object -comobject Excel.Application
  $wbk = $xlApp.Workbooks.Open((resolve-path $filename))
  try
  {
    # check for Office 2013 PowerPivot
    $xlPart = $wbk.CustomXMLParts.SelectByNamespace(
"https://gemini/pivotcustomization/PowerPivotVersion")
    $version = $xlPart.item(1).SelectSingleNode("//ns0:CustomContent")
    $ppVersion = "Workbook last saved by Office 2013 PowerPivot version " + $version.Text
  }
  catch
  {
    try
    {
      # check for Office 2010 PowerPivot
      $xlPart = $wbk.CustomXMLParts.SelectByNamespace(
"https://gemini/workbookcustomization/PowerPivotVersion")
      $version = $xlPart.item(1).SelectSingleNode("//ns0:CustomContent")
      $ppVersion =
"Workbook last saved by Office 2010 PowerPivot version " + $version.Text
    }
    catch
    {
      try
      {
        # check for Office 2010 before PowerPivot version was
        # added to the custom XML
        $xlPart = $wbk.CustomXMLParts.SelectByNamespace(
"https://gemini/workbookcustomization/SandboxNonEmpty")
        $nonEmpty = $xlPart.Item(1).SelectSingleNode("//ns0:CustomContent")
        if ($nonEmpty.Text -eq "1")
        {
          $ppVersion =
"Workbook last saved by Office 2010 PowerPivot “ + "version 10.50.1600.1"
        }
      }
      catch
      {
        # Catch the exception,
        # default version message will be displayed
      }
    }
  }
  $wbk.Close($false)
  $xlApp.Quit()
}
catch
{
  Write-Error $_
}
$ppVersion