CustomPatternClient_Excel_Selector

0.1.0

CustomPatternClient_Excel_Selector.ps1

<#PSScriptInfo
 
.VERSION 0.1.0
 
.GUID 1161c3a1-ae42-4e30-ad3b-c5fe428821cc
 
.AUTHOR damienb@microsoft.com
 
.COMPANYNAME Microsoft Corp.
 
.COPYRIGHT
 
.TAGS
 
.LICENSEURI
 
.PROJECTURI
 
.ICONURI
 
.EXTERNALMODULEDEPENDENCIES
 
.REQUIREDSCRIPTS
 
.EXTERNALSCRIPTDEPENDENCIES
 
.RELEASENOTES
 
 
#>

<#
 
.DESCRIPTION
This script will select the input range or object in the specified sheet. Neither need to be visible.
 
.SYNOPSIS
This script will test the ISheetContentSelect custom pattern
 
.EXAMPLE
CustomPatternClient_Excel_Selector.ps1 -window Book1 -sheet Sheet1 -range A1:D15
 
.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 object
uses the SelectObject method. The object does not have to be visible.
 
.PARAMETER range
uses the SelectRange method
 
#>
Param(
    [Parameter(Position = 0, Mandatory=$True)][string]$book,
    [Parameter(Position = 1, Mandatory=$True)][string]$sheet,
    [Parameter(Mandatory=$False)][string]$object,
    [Parameter(Mandatory=$False)][string]$range
    )

# Setup
Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted
Install-Module -Name YellowBox -Scope CurrentUser -MinimumVersion 0.0.2.0 | Import-Module

# pattern guid
[Guid] $IID_SheetContentSelect        = "5979E0BC-12AC-4105-B10A-2FD04546E9C2"

# method guids
[Guid] $IID_SelectObject = "E36D84D6-B003-47D2-9AC2-9A1B8BA6EB62"
[Guid] $IID_SelectRange = "BB66F5D4-795E-4B6E-9D62-F14804FC240C"

# make sure either object or range specified
if (!($range -xor $object))
{
    Throw "Must specify either object or range to select"
}

# 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_SheetContentSelect, <# out #> $customPattern)

#switch based on whether we got an object or range
if ($range)
{
    Write-Output "Doing selection of range $range"
    $customPattern.CallExtensionMethod($IID_SelectRange, $range)
}
else
{
    Write-Output "Doing selection of object $object"
    $customPattern.CallExtensionMethod($IID_SelectObject, $object)
}