Your automation would first open the workbook in read/write mode. If your worksheet has headers, make sure the “Contains headers” is checked at the Excel Advanced: Open action.
Then use a Loop: Excel Advanced - For each row in worksheet. I’m assuming you have a column header for the country.
One way to check to see if that sheet exists is to use a cell outside of your data (e.g., if you have 5 columns of data, use a cell in column 7, and make sure not to use row 1 which is a header row -- for our example, let’s say cell H2) and use that as a scratch cell. What will you put there? A simple formula to detect if the sheet exists. =sheet(“$mySheetName$”) (Use the Excel Advanced: Set Formula action, and be sure to put quotes around the variable for the sheet name variable.)
Then read that cell back in with an Excel Advanced: Get Cell action. If the value returned is #N/A, then you know the sheet doesn’t exist and you should use logic to create a sheet and set headers. If it returns a number, that is the numeric index for the sheet (e.g., the first sheet reports “1”). That means you can go directly to the sheet with that index and put your data in.
How do you put your data into each sheet? I would recommend using the Set Cell/Go to Cell combination based on the active cell. That way you won’t have to keep track of the position you’re writing to in each sheet.
Interestingly, this is VERY similar to one of our Master Certification use cases several years back. Good luck!
Thank you for your reply. I’m heartened that this has been tackled in the past, but also worried that I’m just beginning to use Automation Anywhere and this is supposed to be my “basic bot” project and not a Master Certification.
I understand the basics of the solution you laid out, but I don’t understand all the steps I’ll need add to accomplish this after the “I have opened the workbook” step in the task bot; the actions/selections in the TaskBot tool are not intuitive, to me.
Shouldn’t I be able to create a blank “unique country” list variable, and then set up a loop to read the country for each employee, compare it to the “unique country” list variable, and use an IF command that basically says, “If this country exists on my list, open the appropriate file and add this record to it, otherwise create a new file for that country and add this record to it?”
Yes you can but that complicates the automation. This is too complex for a "basic bot". You need a good grasp of the Excel Advanced package, along with the Set Formula, Set Cell, Get Cell, Go to Cell, Open, Save, Close, Create Workbook actions; the Loop package; using Record variables; and much more.
I would start your learning with something much simpler. This sounds like a commercial use of our community edition software that is not legally allowed for commercial use. It's for learning only.
Thank you also for your reply. I am currently in an Automation Anywhere class, but my company is sponsoring me; I am using the Community edition. Apologies if this is in the wrong place.
No, you’re in the right place. :)
But my earlier comment stands: This is not a beginner bot. This is very similar (actually slightly harder!) than one of our Master Certification use cases. I could build it quickly because I’ve had over 6 years experience using this software, but for someone just starting out, there is a lot more here than it looks.
Some topics to look at are using the relative cell positioning options for the “Go to Cell” and “Set Cell” actions. They will greatly simplify your automation. This allows you to move the “cursor” (highlighted cell) in Excel rather than using absolute coordinates (e.g., A1). My comment about using the =sheet() function, on the other hand, requires use of absolute coordinates.
