Overview

When using the Excel command to retrieve data from an Excel spreadsheet that has its first row defined as a heading, you need to specify the rows and columns in a manner that they take those headings into consideration.

Contains Headers in Excel operations

Contains Header allows you to retrieve/fetch data from the active spreadsheet that has its first row defined as a header. You can define the option in Open Spreadsheet for a particular session. It is applicable to Excel operations Get Cells, Set Cell, Go to Cell, Find/Replace and Delete Cell for that particular session.

Also, in cases where one or more columns of the target Excel spreadsheet change position due to any update (i.e. introducing new columns/deleting existing columns), the corresponding automation Task Bot / MetaBot Logic has to be updated to accommodate the change. Using 'Contains Header' you can enable the Task Bot / MetaBot Logic to automatically map re-positioned columns of the target Excel spreadsheet.

Excel Cell Variable - a must for Contains Header option

You can use the System Variable - Excel Cell when configuring Contains Header. This variable is exclusive to Contains Header.

Excel Cell should be used while configuring Excel commands Get Cells, Set Cell, Go to Cell, Find/Replace and Delete Cell. During play time, the variable will indicate the column name and position of the cell from the header. It gives the column reference to the defined cell; e.g cell defined is A5, it will refer to Row A , Column 6, if 'Contains Header' is enabled.

When you use this variable, without configuring the Open Spreadsheet command, the program throws an error during play time - "To enable Excel Cell variable configure Open Spreadsheet command."

To know more about the variable, refer Excel System Variables.

The where and how of Contains Header

You can apply the Contains Header option for Excel operations:

• Open Spreadsheet - Contains Header option is controlled from this sub-command as it is configured to the session specified here. Consequent Excel operations will consider the option; whether selected or not for the session specified in this command. Hence, configuring this command is a must.

  Reference Points:

  • When you open multiple spreadsheets during automation, the program considers the last spreadsheet action. It means that if 'Contains Header' is enabled in Session 1 and not in Session 2, the consequent command will not have Contains Header enabled unless you specify the particular session that has Contains Header (Session 1 in this case).
  • In spreadsheets with duplicate header name/content, the program will consider the first occurrence of the header name/content.
  • If you include an invalid value or the header row mismatches, the program will throw an error during play time.
  • You can input maximum three (3) alphabets for a Column title (e.g. ABC); if you input more then you will encounter an error "Check the value specified in the Column Title. You can input up to 3 alphabets only."

• Get Cells - For the 'Get Single Cell' or ' Get Multiple Cells' parameters, include the Excel Cell variable in the 'Specific Cell' or 'From Specific Cell' and 'To Specific Cell' text boxes as shown:

  In the variable, input the cell heading title. For example, if the cell range specified is A5 through A10, define the variable with heading title as given in cell A1; in this case "Id".

  Note: In editions earlier to 8.1, Contains Header was applicable and available in Get Cells command only. If you are re-configuring a task that uses that version with 'Contains Header' enabled, it will be "grayed out." It is recommended that you re-configure the command in the task if you want the task to run properly in the current edition.

  If you encounter an error in using the Get cells operation when the Contains Header option is selected, see Error on Excel Command Get Cells (A-People login required).

• Set Cell - You can apply the 'Contains Header' option to parameters Active Cell and Specific Cell when configuring the Set Cell command. If the cell position shifts, values in the specified cells will be set appropriately.

  You cannot, however, reset value in the cells if the selected cell, is a header cell. You will encounter the error "The specified cell is part of the header row. You cannot set value in it as 'Contains Header' is enabled." during play time.

  You will also not be allowed to set the value of a cell that is defined as a header title. Same is true, if it's in edit mode and the option is enabled.

• Go to Cell - You can Go to a specific or active cell when 'Contains Header' is enabled. All conditions that are applicable during Set Cell are also applicable here.

  Note: In Automation Anywhere Excel Command, Active Cell > End of the row/column refers to "end of the row/column of data"; not the end of spreadsheet row/column.

• Delete Cells - When using this command for 'Contains Header', you will not be allowed to delete the cells that have a header title defined i.e. the first row of the spreadsheet. If you have specified a cell that is defined as a header - either by using the active or specific cell parameter, you will encounter the error "The specified cell is part of a header row. You cannot delete it as 'Contains Header' is enabled." during play time.

• Find/Replace -When using this command for 'Contains Header', you will be allowed to Find a cell value by Row/Column but Replace the content in only those cells that are not defined as header titles. If you specify a cell that is defined as a header, you will encounter the error "The specified cell is part of a header row. You cannot replace its value as 'Contains Header' is enabled." during play time.