Procedures

Automation procedures in the Microsoft Excel BDK Book.

Make sure to add both the Microsoft Excel and the Microsoft Office 365 Books to your agent before using these automation procedures.

to clear the worksheet range

Clear the contents of a range in an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	The reference to the Excel range.	excel range reference	Yes

Examples

Clear the contents of a range in an Excel sheet.

...
get the first sheet
use the above as the sheet
get the sheet's used range
clear the worksheet range

to create a (table) on a worksheet range

Create a table on a specified range in an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	The reference to the Excel range.	excel range reference	Yes
has headers	A boolean value indicating whether the range has headers. Default is True.	boolean	No	True

Output Concepts

Concept	Description	Type
table	The reference to the created Excel table.	excel table reference

Examples

Create a table on a specified range in an Excel sheet.

...
get the first sheet
use the above as the sheet
get the sheet's used range
use the above as the range
create a table on the range

to create a (worksheet range) in a sheet

Create a range in an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
sheet	The reference to the Excel sheet.	excel sheet reference	Yes
start	The start address of the range. For example, "A1".	text	Yes
end	The end address of the range. For example, "B2".	text	Yes

Output Concepts

Concept	Description	Type
worksheet range	The created range.	excel range reference

Examples

Create a range in an Excel sheet.

...
get the first sheet
use the above as the sheet
create a worksheet range in the sheet with the start "A1" and the end "B2"

to delete a column from the table

Delete a column from the table of an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes
column	The reference to the column to delete.	excel column reference	Yes

Examples

Delete a column from the table of an Excel sheet.

...
get the first table
use the above as the table
get the first column
use the above as the column
delete the column from the table

to delete a row from the table

Delete a row from the table of an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes
row	The reference to the row to delete.	excel row reference	Yes

Examples

Delete a row from the table of an Excel sheet.

...
get the first table
use the above as the table
get the first row
use the above as the row
delete the row from the table

to get the (cell's color)

Retrieve the color of a cell in an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	The reference to the Excel cell.	excel cell reference	Yes

Output Concepts

Concept	Description	Type
cell's color	The color of the cell in the sheet.	text

Examples

Retrieve the color of a cell in an Excel sheet.

...
get the first cell
use the above as the cell
get the cell's color

to get the (cell's formula)

Retrieve the formula of a cell in an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	The reference to the Excel cell.	excel cell reference	Yes

Output Concepts

Concept	Description	Type
cell's formula	The formula of the cell in the sheet.	text

Examples

Retrieve the formula of a cell in an Excel sheet.

...
get the first cell
use the above as the cell
get the cell's formula

to get the (cell's value)

Retrieve the value of a cell in an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	The reference to the Excel cell.	excel cell reference	Yes

Output Concepts

Concept	Description	Type
cell's value	The value of the cell in the sheet.	boolean or number or text

Examples

Retrieve the value of a cell in an Excel sheet.

...
get the first cell
use the above as the cell
get the cell's value

to get the (column count) in a table

Get the number of columns in an Excel table.

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes

Output Concepts

Concept	Description	Type
column count	The number of columns in the table.	number

Examples

Get the number of columns in an Excel table.

...
get the first table
use the above as the table
get the column count in the table

to get the (column count) in a worksheet range

Get the number of columns in an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	The reference to the Excel range.	excel range reference	Yes

Output Concepts

Concept	Description	Type
column count	The number of columns in the sheet range.	number

Examples

Get the number of columns in an Excel sheet.

...
get the sheet's used range
use the above as the range
get the column count in the range

to get the (column's cells) from the table

Retrieve the cell of an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes
column	The reference to the Excel column.	excel column reference	Yes

Output Concepts

Concept	Description	Type
column's cells	The list of Excel cells in the table column.	excel cell reference

Examples

Retrieve the cells of an Excel table.

...
get the first table
use the above as the table
get the first column
use the above as the column
get the column's cells from the table

to get the (file's sheets)

FILTER - CAPABLE

Retrieve the sheets of an Excel file.

Input Concepts

Concept	Description	Type	Required	Default Value
file	The reference to the Excel file. The file must be an Excel file stored in a SharePoint document library.	sharepoint file reference	Yes

Output Concepts

Concept	Description	Type
file's sheets	The list of Excel sheets in the file.	excel sheet reference

Examples

Retrieve the sheets of an Excel file.

get the site from sharepoint whose name is "My Site"
get the site's document libraries
get the items from the first library
get the second item
use the above as the folder
get the folder's items
use the above as the responses
get the second response
use the above as the target
get the target's sheets

Retrieve the sheets of an Excel file by name.

get the site from sharepoint whose name is "My Site"
get the site's document libraries
get the items from the first library
get the second item
use the above as the folder
get the folder's items
use the above as the responses
get the second response
use the above as the target
get the target's sheets whose name is "Sheet1"

to get the (following row range) in a worksheet range

Retrieve the following row in an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	The reference to the Excel range.	excel range reference	Yes

Output Concepts

Concept	Description	Type
following row range	The following row in the sheet.	excel range reference

Examples

Retrieve the following row in an Excel sheet range.

...
use the above as the range
get the following row in the range

to get the (row count) in a table

Get the number of rows in an Excel table.

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes

Output Concepts

Concept	Description	Type
row count	The number of rows in the table.	number

Examples

Get the number of rows in an Excel table.

...
get the first table
use the above as the table
get the row count in the table

to get the (row count) in a worksheet range

Get the number of rows in an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	The reference to the Excel range.	excel range reference	Yes

Output Concepts

Concept	Description	Type
row count	The number of rows in the sheet range.	number

Examples

Get the number of rows in an Excel sheet.

...
get the sheet's used range
use the above as the range
get row count in the range

to get the (row's cells) from the table

Retrieve the cell of an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes
row	The reference to the Excel row.	excel row reference	Yes

Output Concepts

Concept	Description	Type
row's cells	The list of Excel cells in the table row.	excel cell reference

Examples

Retrieve the cells of an Excel table.

...
get the first table
use the above as the table
get the first row
use the above as the row
get the row's cells from the table

to get the (row's cells) from the worksheet range

Retrieve the cell of an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	The reference to the Excel range.	excel range reference	Yes
row	The reference to the Excel row.	excel row reference	Yes

Output Concepts

Concept	Description	Type
row's cells	The list of Excel cells in the sheet.	excel cell reference

Examples

Retrieve the cells of an Excel sheet.

...
get the first row
use the above as the row
get the row's cells from the range

to get the (sheet's tables)

FILTER - CAPABLE

Retrieve the tables of an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
sheet	The reference to the Excel sheet.	excel sheet reference	Yes

Output Concepts

Concept	Description	Type
sheet's tables	The list of Excel tables in the sheet.	excel table reference

Examples

Retrieve the tables of an Excel sheet.

...
get the first sheet
use the above as the sheet
get the sheet's tables

Retrieve the tables of an Excel sheet by name.

...
get the first sheet
use the above as the sheet
get the sheet's tables whose name is "Table1"

to get the (sheet's used range)

Retrieve the used range of an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
sheet	The reference to the Excel sheet.	excel sheet reference	Yes

Output Concepts

Concept	Description	Type
sheet's used range	The used range of the sheet.	excel range reference

Examples

Retrieve the used range of an Excel sheet.

...
get the first sheet
use the above as the sheet
get the sheet's used range

to get the (worksheet range's rows)

Retrieve the cell of an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	The reference to the Excel range.	excel range reference	Yes

Output Concepts

Concept	Description	Type
worksheet range's rows	The list of Excel rows in the sheet.	excel row reference

Examples

Retrieve the rows of an Excel sheet.

...
get the sheet's used range
use the above as the worksheet range
get the worksheet range's rows

to get the (worksheet range) from the table

Retrieve the range of an Excel table.

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes

Output Concepts

Concept	Description	Type
worksheet range	The range of the table.	excel range reference

Examples

Retrieve the range of an Excel table.

...
get the first table
use the above as the table
get the worksheet range from the table

to insert a (new column) in the table

Insert a new column in the table of an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes
index	The index at which to insert the new column.	number	Yes
name	The name of the new column.	text	Yes
values	The values to insert in the new column.	boolean or number or text	Yes

Output Concepts

Concept	Description	Type
new column	None.	excel column reference

Examples

Insert a new column in the table of an Excel sheet.

...
get the first table
use the above as the table
insert a new column in the table
... the index is 1
... the name is "Column1"
... the values are "value1", 2, 3.0, True

to insert a (new row) in the table

Insert a new row in the table of an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes
index	The index at which to insert the new row.	number	Yes
values	The values to insert in the new row.	boolean or number or text	Yes

Output Concepts

Concept	Description	Type
new row	None.	excel row reference

Examples

Insert a new row in the table of an Excel sheet.

...
get the first table
use the above as the table
insert a new row in the table
... the index is 1
... the values are "value1", 2, 3.0, True

to read the (content) from a table

Read the contents of an Excel table and return it as a Table object.

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes

Output Concepts

Concept	Description	Type
content	The table content as a table object.	table

Examples

...
use the above as the table
read the content from the table

to read the (content) from a worksheet range

Read the contents of an Excel range and return it as a Table object.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	The reference to the Excel range.	excel range reference	Yes

Output Concepts

Concept	Description	Type
content	The range content as a table object.	table

Examples

...
use the above as the range
read the content from the range

to retrieve the (columns) from the table

FILTER - CAPABLE

Retrieve the columns of an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes

Output Concepts

Concept	Description	Type
columns	The list of Excel columns in the table.	excel column reference

Examples

Retrieve the columns of an Excel table.

...
get the first table
use the above as the table
retrieve the columns from the table

to retrieve the (rows) from the table

FILTER - CAPABLE

Retrieve the rows of an Excel

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes

Output Concepts

Concept	Description	Type
rows	The list of Excel rows in the table.	excel row reference

Examples

Retrieve the rows of an Excel table.

...
get the first table
use the above as the table
retrieve the rows from the table

to retrieve the (worksheet range) from a table

Retrieve the range of an Excel table.

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes

Output Concepts

Concept	Description	Type
worksheet range	The range of the table.	excel range reference

Examples

Retrieve the range of an Excel table.

...
get the first table
use the above as the table
retrieve the worksheet range from the table

to set the cell's content to a value

Set the value of a cell in an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	The reference to the Excel cell.	excel cell reference	Yes
value	The value to set in the cell.	boolean or number or text	Yes

Examples

Set the value of a cell in an Excel sheet.

...
get the first cell
use the above as the cell
set the cell's content to "Hello, World!"

to set the cell's formula to a formula value

Set the formula of a cell in an Excel sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	The reference to the Excel cell.	excel cell reference	Yes
formula value	The formula to set in the cell.	text	Yes

Examples

Set the formula of a cell in an Excel sheet.

...
get the first cell
use the above as the cell
set the cell's formula to "=A1+B1"

to write the content in a table

Set the contents of a table in an Excel table.

Input Concepts

Concept	Description	Type	Required	Default Value
table	The reference to the Excel table.	excel table reference	Yes
content	The table object to set in the table.	table	Yes

Examples

...
use the above as the table
read the content from the table
use the above as the content
create a worksheet range in the sheet with
... the start is "A1"
... the end is "B2"
use the above as the new range
create a table on the new range
use the above as the new table
write the content in the new table

to write the content in a worksheet range

Set the contents of a table in an Excel range.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	The reference to the Excel range.	excel range reference	Yes
content	The table object to set in the range.	table	Yes

Examples

...
use the above as the range
read the content from the range
use the above as the content
create a worksheet range in the sheet with
... the start is "A1"
... the end is "B2"
use the above as the new range
write the content in the new range