| Solution |
To see how the task runs in Automation Anywhere, download the files.
 chkDuplicateEntries_Excl.atmn  TestData.xls
Note: Save the xls in C: drive, the .atmn file in My Documents -> Automation Anywhere -> Automation Anywhere -> My Tasks
Below are the steps that make the Task: |
| 1. | Define Variables |
Go to Variable Manager and create variables "CurCellValue" and "NxtCellValue" |
| 2. | Create new task |
Click on New->Task Editor. |
| 3. | Open Excel sheet |
From Application Category->Excel, Select 'Open Spreadsheet' option. See Example Screenshot 1. |
| 4. | Sort data in Excel sheet |
Insert Keystroke command to sort data in Excel sheet.
Keystrokes: [CTRL DOWN]a[CTRL UP] in "Microsoft Excel - TestData.xls"
Keystrokes: [ALT DOWN]d[ALT UP] in "Microsoft Excel - TestData.xls"
Keystrokes: s in "Microsoft Excel - TestData.xls"
Keystrokes: [DOWN ARROW][DOWN ARROW] in "Sort"
Keystrokes: [ENTER] in "Sort"
Keystrokes: [ENTER] in "Sort" |
| 5. | Retreive value of 1st cell |
Insert Excel command to get the value of the first cell
Excel: Go to cell "A1" |
| 6. | Assign value to variable Clipboard |
Insert Excel command and assign the value of variable to "Clipboard"
Excel: Get value of Active Cell and assign to variable "Clipboard" |
| 7. | Go through all the cells until word 'STOP' found |
Insert Loop for variable "Clipboard" not equal to "STOP". |
| 8. | Check for duplicate value |
Insert a Loop to check value for currently active cell is equal to that stored in the variable "CurCellValue"
Loop While $CurCellValue$ Equal To $NxtCellValue$ |
| 8. | Delete the duplicate row |
Insert Excel command to delete the entire row.
Excel: Delete Active Cell with option entire row Screenshot 2
|
| 9. | If Duplicate value is not found then assign value of cell to the variable |
Insert Excel command to store value to Active Cell to "Clipboard"
Excel: Get value of Active Cell and assign to variable "Clipboard". |
| |
| When you run this task, it will sort the data in an excel sheet. Then it will
loop through each cell in first column until word 'STOP' is found and will delete duplicate entries from the first column.
Screenshot 3 |
Automation Anywhere - The Automation Software
