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
}