So I already have a row of existing data for the RPA to reference. The bot will go down that existing row of data in my Excel sheet (C4:C14), search it on my other app and get the result that is generated for each cell of data. The result that is generated will then need to be copied into I4:I14 of the same sheet.
Can someone help me what function should I use to fill the I4:I14 column of data?
I’ve tried a few methods and they seem to just overwrite I4 continuously.
Are you using Studio or StudioX?
You can achieve this in a couple of different ways.
First off → To improve your current approach, you just need to properly increment your row reference, i.e. to go from I4 to I5, I6 and so on, you need to use a variable e.g. intCounter which you can increment per loop.
and then use it in Write Cell activity such as "I" + intCounter. This way, it will write in the corresponding row.
If you’re using Studio, you might consider overwriting the spreadsheet completely with a new updated datatable object. This is an overhead programmatically, but simpler to implement.
Use Read Range to get worksheet as datatable
Update your datatable object by using simple column update, e.g. myTable(rowCounter)("columnName") = "NewValue"
Here, myTable is your entire sheet as table. rowCounter is your current row which you will increment per loop columnName is the header of your column I (which you want to update)
Use Write Range activity to update the datatable back into the worksheet.
Try this file. The excel workbook has two spreadsheets - ‘Main’ and ‘Test Sheet’
Main shows how the file is BEFORE the run
Test Sheet is the state AFTER the file is updated.
Give it a run and see if this helps clear a few things.
Happy automation!
Hi the zip file was very useful thank you very much. But can I clarify, the derived data I want will be copied form my external app. How do I save my output value into strDerivedValue?
also i have a minor issue now the bot is running well and going through my rows of data C2:C12 but then when it is done with C12 it runs again C2, C3, C4… its an endless loop. Am I doing something wrong?
What is the condition on your for loop?
For example, if you’re running For each row in datatable, then it will automatically exit after all the rows in the table are processed one-time.
However, if you are using a While or Do-While loop then your condition needs to be programmed so that it will become false at some point, thus forcing the loop to finish and move on.
Edit: I just left it running for 20 minutes and it finally exited the loop but my data it updated in the Excel is the data of the very last row. Not the individual values of each row. How do I save the data (copied from external app) to strDerivedValue?
Like this? In the Excel basically im using Lot to find each matching mother lot and want to fill in the respective row. But now as you can see I2 to I12 is filled with the same value which is actually the result for the last row only.
Issues now:
Bot keeps looping through the Lot column MULTIPLE times for 20 mins
You can reference any column value by simply using column name or index such as - row("Lot").ToString or row(2).ToString
This makes it easy to update corresponding column per row. The row object automatically references the current row because it’s your iterator variable (for each row in dtAllRecords)
Inside the For loop For Each row in dtAllRecords you have used Read Range again just for column C (C2:C12). This is not necessary because you already have access to this data from first table.
I have disabled it. This double looping is what was causing your 20 minute runs, because it probably ran 11 x 11 = 121 iterations.
For the second for loop, your iterator variable was also declared as row which meant ambiguous variable declaration. The compiler won’t compain with an error because it can still find the variable but it’s not recommended.
Also, note the Assign activity in the above image.
It assigns strLot the value row.item(0).ToString i.e. the first row in each loop.
As a result, you had the same value repeating over all the rows because it only looked up one value.
Here is the updated workflow, please try working with this and update as necessary. Dummy mlot.xaml (36.5 KB)