POWERSHELL - CHECK POWERPIVOT VERSION

TWO POEWRSHELL FUNCTIONS ARE USED FOR CHECKING POWERPIVOT VERSION

# The GetPowerPivotVersion function returns the 
# the build number of the Microsoft.Office.PowerPivot.ExcelAddIn.dll
# that was last used to save the PowerPivot model.
# For example:
#     10.50.1600.1 corresponds to  SQL Server 2008 R2 RTM PowerPivot for Excel 2010.
#     11.0.2100.60 corresponds to  SQL Server 2012 RTM PowerPivot for Excel 2010.
#     00.0.0000.00 indicates that the specified workbook is not a PowerPivot workbook.
Function GetPowerPivotVersion($fileName)
{
    # Initially, assume this isn't a PowerPivot workbook.
    # i.e. there is no PowerPivotVersion.
    $ppVersion = "00.0.0000.00"

    try
    {
        # Start Excel and open the workbook.
        $xlApp = New-Object -comobject Excel.Application
        $wbk = $xlApp.Workbooks.Open($fileName)

        try
        {
           # Retrieve the version info from the PowerPivotVersion custom XML part.
           $xlPart = $wbk.CustomXMLParts.SelectByNamespace("http://gemini/workbookcustomization/PowerPivotVersion")
           $version = $xlPart.Item(1).SelectSingleNode("//ns0:CustomContent")
           $ppVersion = $version.Text
        
        }
        catch
        {
          try
          {
            # The PowerPivotVersion custom XML part was not found. 
            # Check the SandboxNonEmpty value to determine if this is a 2008 R2 RTM workbook.

            $xlPart = $wbk.CustomXMLParts.SelectByNamespace("http://gemini/workbookcustomization/SandboxNonEmpty")
            $nonEmpty = $xlPart.Item(1).SelectSingleNode("//ns0:CustomContent")
            if($nonEmpty.Text -eq "1")
            {
                # SandboxNonEmpty value is 1, so this is a 2008 R2 RTM workbook.
                $ppVersion = "10.50.1600.1"
            }
          }
          catch
          { # SandboxNonEmpty not found = plain workbook.
            # Just suppress the exception...
          }
        }

        # Close the workbook and quit Excel
        $wbk.Close($false)
        $xlApp.Quit()
    }
    catch
    {
        Write-Error $_
    }
    
    #return the results
    return $ppVersion
}

# Iterates through the specified document library in the referenced site
# and returns the PowerPivot version for all workbooks in this library
# in form of a hashtable.
Function GetWorkbookVersions($siteUrl, $docLib)
{
    # Create an empty hashtable
    $wbkInfo = @{}
        
    try
    {
        # Instantiate the SharePoint Lists Web Service
        $uri = $siteUrl + "/_vti_bin/Lists.asmx?wsdl"
        $listsSvc = New-WebServiceProxy -Uri $uri -UseDefaultCredential

        # Create the request to retrieve all the default fields
        $xmlReq = New-Object -TypeName System.Xml.XmlDocument
        $viewFields = $xmlReq.CreateElement("ViewFields")
          
        # Get the list of all the items in the document library
        $nodeListItems = $listsSvc.GetListItems($docLib, $null, $null, 
                                                $viewFields, $null, $null, $null)
        
        # Iterate through the items in the list
        foreach($item in $nodeListItems.data.row)
        {
            # Make sure we are dealing with a workbook
            if($item.ows_DocIcon -eq "xlsx")
            {
                # Get the PowerPivot version and add it 
                # together with the workbook URL to the hashtable
                $wbkInfo[$item.ows_EncodedAbsUrl] = GetPowerPivotVersion $item.ows_EncodedAbsUrl
            }
        }
    }
    catch
    {
        Write-Error $_
    }
        
    # Return the results
    return $wbkInfo
}