CustomPatternClient_Excel_Selector

0.1.1

CustomPatternClient_Excel_Selector.ps1

<#PSScriptInfo
 
.VERSION 0.1.1
 
.GUID 1161c3a1-ae42-4e30-ad3b-c5fe428821cc
 
.AUTHOR damienb@microsoft.com
 
.COMPANYNAME Microsoft Corp.
 
.COPYRIGHT
 
.TAGS
 
.LICENSEURI
 
.PROJECTURI
 
.ICONURI
 
.EXTERNALMODULEDEPENDENCIES
 
.REQUIREDSCRIPTS
 
.EXTERNALSCRIPTDEPENDENCIES
 
.RELEASENOTES
Added support for window names with file extensions and books that have been saved with a new name.
 
#>

<#
 
.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 -book Book1 -sheet Sheet1 -range A1:D15
 
.EXAMPLE
CustomPatternClient_Excel_Selector.ps1 -window "NewBookName.xlsx - Excel" -pane BookNameonOpen -sheet Sheet1 -object Chart1
 
.PARAMETER book
Workbook name without the file extension. It is used to identify the window root,
and the second level child pane. This option only works if the workbook name has
not changed and file extensions are not shown in file explorer. Otherwise, you must
provide explicit naming for the parent window name and the pane name.
 
.PARAMETER window
The UIA window name. If you specify a book param, this is assumed to be book - Excel.
If file extensions are turned on, then you need to specify the full name book.xlsx - Excel.
 
.PARAMETER pane
The UIA name of the pane representing the book view. If you specify a book param, this is
assumed to be simply book. If the file has been renamed since opening, this pane will still
be named according to book name on open.
 
.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(Mandatory=$False)][string]$book,
    [Parameter(Mandatory=$False)][string]$window,
    [Parameter(Mandatory=$False)][string]$pane,
    [Parameter(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
if ($book)
{
    $windowName = "$book - Excel"
    $paneName = "$book"
}
elseif ($window -and $pane)
{
    $windowName = "$window"
    $paneName = "$pane"
}
else
{
    Throw "Must specify either book or both window and pane arguments"
}

# See if we have a window by that name
$windowElement = Select-UIXPath "Window[@Name = `"$windowName`"]"
if ($null -eq $windowElement)
{
    Throw "Failed to find $windowName"
}

# Find the pane within the window
$paneElement = Select-UIXPath "Pane/Pane[@Name=`"$paneName`"]" $windowElement
#if ($null -eq (Select-UIXPath "Window[@Name = `"$windowName`"]/Pane/Pane[@Name=`"$paneName`"]"))
if ($null -eq $paneElement)
{
    Throw "Failed to find pane $paneName in $windowName"
}

# the pattern is on the sheet. Get the element for the sheet
$sheetPaneName = "Sheet " + $sheet
$sheetElement = Select-UIXPath "Pane[@Name=`"$sheetPaneName`"]" $paneElement
if ($null -eq $sheetElement)
{
    Throw "Failed to find $sheetPaneName in pane $paneName in window $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)
}