How to Loop in Excel

Looping in Excel is in many ways similar to looping in HTML, but where the possible ways of looping in HTML are very diverse, it is much simpler in Excel due to the simpler structure of Excel. Essentially you may loop over all the sheets in a document or you may loop over the cells of a sheet either by looping over the rows, columns or cells of a found range. To loop in Excel you use the Loop in Excel step. This step has many options in common with steps that loop in HTML, e.g. First index, Increment, etc. and these are described in detail in the reference documentation.

Below is a video explaining how to loop over a table in a single sheet, extract the cells of each row into a variable of complex type and return these with a Return Value step.

Video Tutorial on Looping in Excel

Looping over Sheets and Rows

Generally looping in Excel is a bit more complex than show in the video above. An often occurring scenario for looping in an Excel document is when the document contains a number of sheets containing tables with data of the same type, e.g. one sheet with account information for each month of the year. In this case you would like to have you robot first loops over the sheets and then over say the rows of each sheet. We would probably also like to be able the handle the situation where the document contains a sheet that does not contain data of the same type as the other sheets, e.g. maybe where a sheet is blank. The image below shows the structure of such a robot.

Looping over Sheets and Rows in Excel

The first step in this robot is a Load Page step that loads the Excel document from some URL, the robot then contains a Loop in Excel step that loops over all the sheets of the document. For each iteration of this first loop step the robot executes another Loop in Excel step that loops over each row of the sheet much in the same way as was demonstrated in the video above. The step looping over rows has its Error Handling property Then set to "Next Iteration", which means that if the range finder of the step fails to match a range the size of the table it will go the the next iteration. This simplified error handling will handle the simple situation where a sheet is blank, but not situations where a sheet contains a table with entirely different type of data. In the general case one would have to insert a step to extract part of the sheet followed by a step to test the structure of this. One example of this could be extracting the column headers and testing that these have some given structure. The image below shows the robot extended with this. In this the Extract Cell step named Extract Headers extracts the first row of the sheet into a variable and the Test Value step has a condition that tests the value of this. If the value matches then the robot will execute the next step (the Loop Rows step), if not the robot will skip the following steps, that is, the Do property of the Test Value step has been set to "Skip Following Steps".

Looping over Sheets and Rows in Excel with Test

Looping and Merged Cells

The next scenario that we will look at is when an Excel Page that you want to loop over has merged cells. A merged cell in Excel is two or more adjacent cells that have been merged into one cell and shown as one. The content of a merged cell is stored in the upper left cell of the cells and all other cells are blank. In some case this can give problem when looping over a table that contains such merged cells. Let us look at a simple example that illustrates this. If you look at the sheet below that shows test results for students you will notice that some student have missed their test and and in some case two test shown using a merged cell.

Looping over Sheet with Merged Cells

If we want to extract the students' test results by looping over the rows of the sheet, we might fail to extract the results correctly when a student has missed a test since the text "Missed" is not a number. We could solve this by inserting a test for this and then store the value 0 for a failed result. This would work fine for the cell B4 since this contains the value "Missed", but it would fail to work for C4 since the content of this would be a blank value. Instead of having yet another test for whether the content of the cell is blank, there is a simple option on all Range Finders that ensures that if it finds a single cell inside a merged cell, then it will instead return the upper left cell of the merged cell. The image below show how to configure a range finder in this way.

The Range Finder

Now all the Extract cell has to do is to test for the text "Missed" and use 0 for the result and otherwise use the extracted value. This may be done with an If Then data converter as shown below.

The Extract Cells Action