Excel Custom Patterns
This topic describes the custom UI Automation patterns provided in Microsoft Excel for Windows. These patterns can be queried and used from the provider node for a specific Excel worksheet.
For experimentation purposes, we have created special PowerShell scripts in the PowerShell Gallery that exercise each Excel custom pattern. These are called out explicitly with each pattern, or you can search the PowerShell Gallery for CustomPatternClient_Excel to see all of the Excel scripts. These scripts can be run in a PowerShell window against a running instance of Excel on Windows 11 to see what output the custom patterns produce. The scripts are self documented with comment based help.
The main goal of these patterns is to provide sheet information that is not constricted to the visible area of the sheet. Names and ranges are the mode of communication, rather than accessibility objects. For cells, the name as used in these patterns is also available as the UIA_AutomationIdPropertyId.
Operands common to all methods
There are 3 fixed input operands for every custom pattern method call.
| Operand | Type | Description |
|---|---|---|
| 0 | ConnectBoundObject | Pattern Object |
| 1 | GUID | The method Guid |
| 2 | Int32 | Total number of input and output arguments |
Return values
Every method described here returns an HRESULT for the CallExtension to indicate success or failure. A successful operation returns S_OK, and a failed operation will yield UIA_E_INVALIDOPERATON.
ISheetCellInventory
Description
Guid: {654823FE-A483-4915-8709-67266866E518}
This is a pattern on a worksheet that collects cells that match certain traits. In all cases, they return a collection of strings that are cell names
PowerShell Script
CustomPatternClient_Excel_Cell_Inventory.ps1
Methods
GetCellsWithFormula
Guid: {24E137F2-4FFF-4F50-84AD-2ACD780E7E1F}
Returns the names of cells in the worksheet that have formulas
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Cell names |
GetCellsWithHyperlink
Guid: {B640F40E-E51A-4CDD-9604-843AA5107C1C}
Returns the names of cells that have literal hyperlnks. In other words, cells with a value that is a URL.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Cell names |
GetCellsWithFormulaHyperlink
Guid: {0AAF2B49-015B-4AB3-A093-21F055E8E0F5}
Returns the names of cells that have formula hyperlnks. In other words, cells that use the HYPERLINK function in their formula.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Cell names |
GetCellsWithNote
Guid: {8F171892-A3B1-4F92-9CE1-CE818B848F52}
Returns the names of cells that have a note or nonthreaded legacy comment associated with them.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Cell names |
GetCellsWithComment
Guid: {4D4E49BC-CE16-44CD-AC48-1DAA2E862C41}
Returns the names of cells that have threaded comments associated with them.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Cell names |
ISheetRangeInventory
Description
Guid: {62F8DBCE-13DC-4EF2-AF53-5247ED2A4980}
This worksheet pattern collects ranges of cells. These methods return a collection of strings that represent a range of cells. The range can be described by their name (where applicable) and optionally the cell range reference, separated from the name by an input delimiter string.
PowerShell Script
CustomPatternClient_Excel_Range_Inventory.ps1
Methods
GetTableNamesAndRanges
Guid: {3FB8E49A-BB4A-4B6C-8B25-CB7E612DA92E}
Gets the names of list tables and their ranges if requested. The range specifications are separated from the table name by the input delimiter string.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Input | Boolean | Include range specifications |
| 4 | Input | String | Name and range field separator |
| 5 | Output | StringArray | Range names and references if requested |
GetPivotTableNamesAndRanges
Guid: {B22CD312-E265-49AE-9C73-11CE3D7B8526}
Gets the names of pivot tables and their ranges if requested. The range specifications are separated from the table name by the input delimiter string.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Input | Boolean | Include range specifications |
| 4 | Input | String | Name and range field separator |
| 5 | Output | StringArray | Range names and references if requested |
GetNamedRanges
Guid: {EFC59F27-E965-48A5-8C79-7BB9E70958FD}
Gets the named ranges and their range specifications if requested. These are cell ranges that have been named using the name manager, and do not include those that come from tables. The range specifications are separated from the table name by the input delimiter string.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Input | Boolean | Include range specifications |
| 4 | Input | String | Name and range field separator |
| 5 | Output | StringArray | Range names and references if requested |
GetUnnamedDataRanges
Guid: {60EBB5D1-299D-471D-8DAB-00EE39EBA79E}
Gets ranges of cells with data that are not otherwise part of a named range or table.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Range references |
GetRowRangesOnPageBreaks
Guid: {2544B784-764D-4EAA-A72F-BABBF28B5504}
Gets single row range references spanning the worksheet's printable area that precede a horizontal page break.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Range references |
GetColumnRangesOnPageBreaks
Guid: {BEA372DC-2FD8-4852-A8BC-2DA34CEA5456}
Gets single column range references spanning the worksheet's printable area that precede a vertical page break.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Range references |
ISheetObjectInventory
Description
Guid: {62F8DBCE-13DC-4EF2-AF53-5247ED2A4980}
This worksheet pattern returns a collection of named objects on the sheet of a specified type.
PowerShell Script
CustomPatternClient_Excel_Object_Inventory.ps1
Methods
GetChartNames
Guid: {6D199DC0-7AF0-43CC-A572-8CF9EE556F75}
Gets the names of charts on the sheet.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Chart names |
GetSmartDiagramNames
Guid: {AF8706FA-6389-4F90-9EF8-F64AED618C4F}
Gets the names of Smart Art diagrams on the sheet.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Diagram names |
GetSlicerNames
Guid: {7D6295DF-E9CC-4B86-BE9F-60BACCB7289D}
Gets the names of slicer objects on the sheet.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Slicer names |
GetTimeSlicerNames
Guid: {F8768BF3-9733-4BD9-9251-507649FF3257}
Gets the names of time slicer objects on the sheet.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Time slicer names |
GetImageNames
Guid: {A2807141-BA1F-4088-85A1-4C00F140998C}
Gets the names of picture objects on the sheet.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Picture names |
GetShapeNames
Guid: {C50C4B9E-2F62-4E47-993D-A961D0C6A03D}
Gets the names of drawing shapes on the sheet.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Output | StringArray | Shape names |
ISheetContentSelect
Description
Guid: {5979E0BC-12AC-4105-B10A-2FD04546E9C2}
This pattern provides the means to select any cell, cell collection or object on the sheet, regardless of the current view state. Cells that are not visible have no accessible element to exercise a selection item pattern on, so this pattern allows selection by cell name. The cell name is also the AutomationID property if wanting to return to a previously visited cell. This method also allows specifying a range of cells to select of the form :. Objects can also be selected by their name as returned by ISheetObjectInventory.
PowerShell Script
CustomPatternClient_Excel_Selector.ps1
Methods
SelectRange
Guid: {BB66F5D4-795E-4B6E-9D62-F14804FC240C}
Selects and makes visible a cell or range of cells on the sheet.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Input | String | Cell name, range reference or range name |
SelectObject
Guid: {E36D84D6-B003-47D2-9AC2-9A1B8BA6EB62}
Selects and makes visible an object on the sheet.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Input | String | Object name |
ISheetCellValue
Description
Guid: {238037C4-BBA3-4C0E-9371-66046B81E957}
This pattern is used when wanting to retrieve the current value of a given cell on the sheet, Whether it is visible or not. The cell is represented by name. The output is a string with the current cell value.
PowerShell Script
CustomPatternClient_Excel_Cell_Value.ps1
Methods
GetCellValue
Guid: {4C6CA843-D4F0-4CB6-B1AF-EF8DADEE9B2C}
Gets the current value of the cell.
| Operand | Role | Type | Description |
|---|---|---|---|
| 3 | Input | String | Cell name |
| 4 | Output | String | Current value of the cell |
Requirements
Microsoft 365 Version 2107 (Build 14326.xxxxx)
See also
Custom Properties, Events, and Control Patterns