Loop Over Sheets and Rows

You can create a robot to loop in an Excel document with multiple sheets containing tables and with the same type of data. For example, each sheet in the Excel spreadsheet to display account information for a separate month of the year. In this case, you would have your robot first loop over the sheets and then over the rows of each sheet. You may also like to handle situations where the document contains a sheet that does not contain data of the same type as the other sheets, such as a blank sheet. The following image shows the structure of such a robot.

The first step in this robot is a Load Page step that loads the Excel document from a 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. The Error Handling property Then of the step that loops over rows is set to Next Iteration, which means that if the range finder of the step fails to match a range with the size of the table, it goes to 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 types of data. In general, you would have to insert a step to extract part of the sheet followed by a step to test the structure. One example could be extracting the column headers and testing that they have some given structure. The following image shows the error handling added to a robot.

In this example, 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. If the value matches, the robot executes the next step (the Loop Rows step). If not, the robot skips the following steps; the Do property of the Test Value step will Skip Following Steps.