CustomPatternClient_Excel_Cell_Value.ps1
|
<#PSScriptInfo
.VERSION 0.1.0 .GUID ba2e4903-bbef-4a73-9a72-7b546cb037b0 .AUTHOR damienb@microsoft.com .COMPANYNAME Microsoft Corp. .COPYRIGHT .TAGS .LICENSEURI .PROJECTURI .ICONURI .EXTERNALMODULEDEPENDENCIES .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES #> <# .DESCRIPTION This script will write the value of the input cell to the console. .EXAMPLE CustomPatternClient_Excel_Cell_Value.ps1 -window Book1 -sheet Sheet1 -cell B15 .EXAMPLE CustomPatternClient_Excel_Cell_Value.ps1 Book1 Sheet1 B15 .PARAMETER book Workbook name without the file extension. It is used to identify the window root. .PARAMETER sheet The name of any sheet tab in the Excel window. It does not have to be active. .PARAMETER cell The cell name/location to be queried for value. The cell does not have to be visible. #> Param( [Parameter(Position=0, Mandatory=$True)][string]$book, [Parameter(Position=1, Mandatory=$True)][string]$sheet, [Parameter(Position=2, Mandatory=$True)][string]$cell ) # Setup Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted Install-Module -Name YellowBox -Scope CurrentUser -MinimumVersion 0.0.2.0 | Import-Module [Guid] $IID_SheetCellValue = "238037C4-BBA3-4C0E-9371-66046B81E957" [Guid] $GetCellValueId = "4C6CA843-D4F0-4CB6-B1AF-EF8DADEE9B2C" # root element is the top level book pane $windowName = "$book - Excel" # the pattern is on the sheet. Get the element for the sheet $sheetPane = "Sheet " + $sheet $sheetElement = (Select-UIXPath "Window[@Name = `"$windowName`"]/Pane/Pane[@Name=`"$book`"]/Pane[@Name=`"$sheetPane`"]") if ($null -eq $sheetElement) { Throw "Failed to find $sheetpane in $windowName" } # Get the custom pattern $customPattern = [YellowBox.Client.ExtensionMethodContainer]::new() $sheetElement.CallExtensionMethod($IID_SheetCellValue, <# out #> $customPattern) $cellValue = [YellowBox.Client.ExtensionMethodArgument]::new() Write-Output "Doing evaluation of cell $name" $customPattern.CallExtensionMethod($GetCellValueId, <# in #> $cell, <# out #> $cellValue) if ($null -eq $cellValue) { Throw "GetCellValue returned null value" } Write-Output "GetCellValue returned $($cellValue.Value)" |