Excel - Get Single Cell vs Get Multiple Cells vs Get All Cells

I experimenting with AA and I get the difference between the three types of "Get Cell" commands, it seems a little easier when working with lots of data to get each single cell and save it to a variable versus Get Multiple Cells or Get All Cells. If I want to do something with the data, I have to save each data to a variable anyway so why use the other commands. Plus, if I use the Get All Cells doesn't it eat up memory. Can anyone provide some basic insight as to when to use each command and how. I ask how because there are various system variables like excel cell column, excel cell row, excel column that can be used with these commands that can also be confusing. Appreciate the insight.

Tagged:

Comments

  • Hi Sean

    We cover a lot about Excel in our training courses, for instance :

    https://www.extratechnology.com/training/automation-anywhere-courses/version-11-hands-on-advanced-workshop-course [ Which is called ETTRAA005v11 ]

    Firstly it all depends on what you are trying to do with your data. Get All Cells will potentially use up a lot of memory is you are working with large spreadsheets.

    When you use 'get all cells' you get access to the 'system' variables when you use the Loop command 'Each row in an Excel dataset'

    The data from each cell in every row can be extracted using $Excel Column(column number)$. So you can move across the spreadsheet by referencing each column by number

    So for instance Column A would be $Excel Column(1)$, B would be $Excel Column(2)$ etc.

    For each iteration of the loop you retrieve row 1, row 2 to row n of your spreadsheet ( or selection of spreadsheet if you used get multiple cells option )

    However you can access excel values by making use of an ODBC connection to the excel spreadsheet. This is a much neater and less memory intensive mechanism.

    My colleague, Bhupinder Janjuha has produced a great blog about this very method which can be seen here:

    http://www.anyautomationanswers.com/discussion/102/accessing-a-ms-excel-spreadsheet-from-automation-anywhere-enterprise-as-if-it-was-a-database#latest

    This effectively means you don't have the memory overhead of having to have MS Excel Open and once you've read the data from the excel spreadsheet, you can access it using the Loop for each row in a SQL Dataset as the spreadsheet effectively becomes a 'table' which you can write regular Microsoft type SQL against.

    In a nutshell, the beauty of Get All / Multiple cells is that you can iterate round each row of your whole spreadsheet or your selection using the loop command.

    Using get single cell, you've have to get the value into a variable and then move right, get the next value, move right again ( to the end of the row ) and then move down , back to the beginning of the row and start again. It would be a lot harder and use far more commands. Its very much horses for courses and the way you do it would depend on exactly what you are trying to achieve.

    One more thing. If the data in excel can be saved to a CSV file, this might be a simpler way or dealing with it as we can simply make use of the Read from CSV/Text option in AA which again will allow you access to each row using the $Filedata Column$ variable. As with the $Excel Column$ we access each column in the file using an integer which denotes is position in this case in the file.

    On our training courses we spend a good deal of time dealing with excel commands and showing their usage. Not to mention reading data from CSV and TXT files, databases and XMLfiles etc.

    Hope this helps.

    Kind regards

    Damian Griffiths
    Practice Manager - Extra Technology

Sign In or Register to comment.