Skip to main content

Working with Excel Automation

The Excel activities help users to automate all aspects of Microsoft Excel. It is an application intensely used by many in all types of businesses.

Most of our Excel activities takes inputs from user where they can specify the Sheet/Range/Cell. Here, we have provided you with details about setting these inputs.

Sheet Index vs. Sheet Name

A workbook can contain one or more sheets. You need to specify to the activity the index of the sheet to be used to perform the task. The index is zero-based and starts from left to right in the workbook.

For example, consider a workbook containing three sheets as indicated in the image below:

Use the following index mapping:

Sheet NameSheet Index
Sheet10
Sheet21
Sheet32

Note: If Sheet Name or Sheet Index is not provided, the activity will consider the active sheet in the current workbook.

Column Index vs Column Letter

A Column Index represents a particular column in a sheet. It is a zero-based index starting from left most column towards the right of the sheet.

In Excel, the columns are represented using letters, and hence you can refer to the following examples, which map the corresponding letters to a numeric index.

Column LetterColumn Index
A0
B1
C2
......
Z25

Row Index

A Row Index represents a particular row in a sheet. It is a zero-based index starting from the topmost rows and moving towards the bottom.

Cell

A cell represents one specific cell inside a sheet. It is a combination of Column Letter and Row Index.

Note: The Row Index is zero-based. However, when used to represent a Cell, the topmost row will start from 1. Hence, ensure that you do a plus one to the row index while building cell address

Please refer to the table below, which denotes some of the examples for cell addresses:

Cell AddressDescription
"A1"The first cell in the sheet whose Column Letter is A and Row Index is 0
"D55"The cell whose Column Letter is D and Row Index is 54

Range

A Range specifies a range of cells in a sheet. It is specified as a string value. A Range string has the following formats:

For example: "A1:B10", "A1"

Adding Range in the form of string

Note: If you do not provide the End Cell for Range, the last cell which has the data in the sheet starting from the specified Start Cell will be used as End Cell

Converting numeric index to letter

Sometimes, while designing process automation, converting Column Index to Column Letter becomes necessary. You can convert the numeric index to a letter-based Column Index by using IntelliBuddies Excel utilities.

Similarly, converting a letter based column index to a numeric based column index could be achieved by using the following expression:

IntelliBuddies.Activities.Excel.ExcelExtensions.ToIndex ("A")