Fill in a column of data being retrieved from elsewhere

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.

  1. Use Read Range to get worksheet as datatable
  2. 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)
  3. Use Write Range activity to update the datatable back into the worksheet.

I hope this helps.

Hi I’m using Studio. For 1. do I select the whole worksheet or just the table part of the worksheet?

Also how do I update datatable? Do u mind showing some examples for me please? I’m quite new to this

Hi @natashagc (9.8 KB)

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!

1 Like

Hi @natashagc
If this helped your problem, please mark the answer as a solution so that this thread will close automatically.

Glad to be of help!

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.

Im using a For each row but it does not exit?

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?

Will you be able to share your workflow file?

dummy mother lot - (19.5 KB)

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:

  1. Bot keeps looping through the Lot column MULTIPLE times for 20 mins
  2. Data copied is not per each respective row

I see the problem with the logic. Here is a point-based explanation of different issues you have.

  1. The table is read completely in the first go with the first Read Range activity

    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)
  2. 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.
  3. 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.
  4. 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)

Oh my god it works now… I am so thankful for u I owe u my life king

1 Like

Haha… Glad to be of help.

Happy automation!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.