Hi All,
I am looking to write my variables in consecutive order in a certain column in excel (A2 → A3 → A4, ect.). I am trying to start in column G for variable “Confirmation_Number” and column H for variable “Confirmation_Amount”. I know I need to change my range, but I’m not sure exactly what to?
Thank you in advance!!
Hello,
Ok, one way is to do what you are doing and add in a counter variable that increments for column or row value.
For example:
Do While
Assign n = n+1
Write Cell with range Convert.ToChar(n+64).ToString+“2”
or
Write Cell with range “A”+n
As you can see, you can convert the number to a letter that represents the column letter if you want.
Similarly and the more ideal way, is to make use of DataTables, so you can use Read Range, then using a For Each row, you can assign the variable to a spot in the column of each row.
For Example,
ForEach row in datatable
Assign row.Item(n) = variable
After all variables have been stored into datatable you can use Write Range to overwrite the spreadsheet.
Thanks. Hope this helped a little bit, lol.
Hi @ClaytonM,
Thanks for the response! I keep getting an error message when running the bot with an “N” variable.
Instead, I took the second approach and made my columns variables and my screen scraping values variables. For some reason though, I cannot get these variables to write in excel using the “Write Cell” feature (excel file shows up blank).
Can you possibly take a look at my bot to see where my mistake is?
Assigned columns as variables:
Grabbing variables and trying to post in excel:
Hi,
First thing I noticed is that you are using a ForEach row but you use column. You are wanting to loop through each Row correct? Then I would rename “column” to "row.
You will also want to have a column in the table to where to store the data you want to add. If there isn’t already a column(s) you can use “Add Data Column” directly after you do “Read Range”.
So, when you get to where you store the CFR_Num and CFR_Amount (toward the bottom) you will want to put in a Message Box “temporarily” to make sure you are getting the correct data directly after the “Get Text”.
To store that data into the table use “Assign”.
It should look something like this:
The Assign activities look like this where the column name is used in the parenthesis.
row.Item(“ConfirmationNumber”) = CFR_Num_From_MM
row.Item(“AmountPosted”) = CFR_Amount_From_MM
And, the 2 Write Cells are replaced with 1 Write Range where you overwrite the table with the new data.
Alternatively, you can get rid of the Assign activites by using row(“column”) or row.Item(“column”) directly in the fields such as in the Output parameter for the Get OCR Text or the Input Intos, which could simplify the sequence.
Well, anyway, hope this helps!
Thanks.
Thanks for the response. I want to keep the “for each row” function in column format. Also, I do not want to assign those two variables in the same activity because they are completely different.
ConfirmationNumber = a column name in excel
AmountPosted = a column name in excel
CFR_Num_From_MM = a value from web application
CFR_Amount_From_MM = a value from web application
I really am just stumped on why this does not work? I assigned the value from web application to CFR_Num_From_MM and then wanted to paste into the excel column ConfirmationNumber…
@TSummers1, yes looked like you were using “ConfirmationNumber” and “AmountPosted” as your column name.
If you look at the images I posted above.
The ForEach you are using loops through each row, then you access each column from that row.
(Referencing the above images)
ForEach row in mydata
Account = row.Item(“Client Account Number”)
row.Item() or row() will access the column within that row. Technically you can use the variable name “column” but you are really looking at the row so “row” makes more logical sense is all 
Now, the important thing to know is that you can change the value of a column within that row to include the CFR_Num and CFR_Amount. You can simply do that with an Assign activity like:
row.Item(“CFR_Num”) = CFR_Num_From_MM
row.Item(“CFR_Amount”) = CFR_Amount_From_MM
Please reference the images above as example as well.
And, like I mentioned if the column does not exist that you want to store the CFR_Num and CFR_Amount in, you will need to use “Add Data Column” right after you do the “Read Range” initially.
OKAY, after you have assigned the CFR_Num and CFR_Amount into the datatable which is also shown in the images above that you can reference, you will just do your Excel Scope with “Write Range”
All you need to write is the entire datatable with the updated columns that were done using the Assign activities shown in the images above.
Hope that cleared up some things.
Let me know if these updates help you out.
Thanks.
Hi @ClaytonM,
Thank you for the response, I appreciate the time. I have followed the pictures and your advise, but this does not seem to work in my bot - thanks anyway!
Tanner
Hi @ClaytonM,
Sorry for multiple question but I’m new and stuck! 
For more clarity, my excel columns are labeled as Confirmation Number (G) and Amount Posted (H). I have made both of these columns variables (going with column vs. row since that is how all my variable are set up and works good).
Then I make a new variable “CFR_Num_From_MM”, and use the “Write Cell” function.
“Journal” = my tab name
“ConfirmationNumber” = the variable that I assigned to column name Confirmation Number in Excel
“CFR_Num_From_MM” = the variable I assigned to the OCR value scraped from the screen
This should work, no? Sorry again for my confusion…I think I am close but missing something. I have attached my project.Main.xaml (70.2 KB)
It’s ok. I think you are confused at what the ForEach is doing…
Let me try to explain that better.
You have a datatable called “mydata”
and You have your 2 columns “Confirmation Number” and “Amount Posted”
What the ForEach does is it loops through each “row” (not column) and then you access each column within the row by using your column names.
For example:
ForEach row In mydata
Then inside that loop you can store data into each column of that row by using the following:
Assign row(“Amount Posted”) = CFR_Amount
and
Assign row(“Amount Posted”) = CFR_Num
Once you have stored all the data you wish to store you can use Write Range to write the entire datatable “mydata” back into the spreadsheet.
Let me upload your Main.xaml with my changes. Thanks.
Main (4).xaml (69.7 KB)
The key changes were I moved your Excel Scope slightly and changed the Write Cells to a Write Range, and placed Assign activities to store the CFR_Num and CFR_Amount into the columns. I also replaced the word column with row in all your assign activities.
If there are issues then it could be with the Web steps also since I didn’t look at that section at all.
Also, if there is further confusion let me know. I’m guessing you want to go through each row of the table then input each column into the site and retrieve CFR_Num and Amount which I hope I helped you with.
Thanks.
Hi @ClaytonM,
Thanks for the clarification, it really helped!
Hi @ClaytonM,
Accidentally sent the message without finishing…
I also rearranged the functions like you did. I am now able to have my variables post, but I am having trouble dynamically doing so. In the below picture, I am assigned “AmountPosted” to the row called “Amount Posted” and “ConfirmationNumber” to the row called “Confirmation Number”.
In the “Excel Application Scope”, I tried using “Write Range”, but with little success. I do not want to have to create a whole new data set to paste these confirmation numbers into, rather just put them into a new column in my date set.
In the below image, I am able to have my variable “CFR_Amount_From_MM” post when I assign a specific cell (G2), but when I try to have it post to the variable “ConfirmationNumber” row, the excel file is blank (please see picture below). I figured since assigned “ConfirmatioNumber” to the row “Confirmation”, it would post to that cell…
Sorry, I am unable to open your file Main, I have firewall on my computer that I am unable to take down…
The first 2 Assigns you have for AmountPosted and ConfirmationNum are currently storing the value that is in that column of the datatable row. It is not assigning the range or column or anything like that. So, I think you are confused there.
In order to use Write Cell you must know the range of that column, however, when you are using a datatable the columns are located by Index numbers and not Excel ranges. Technically you can do that using some more complicated calculations. But, I honestly don’t know why I should direct you down that complex path, lol.
Can you just show a screenshot of your Write Range you have that doesn’t work, instead of Write Cells?
The correct design should show you having 2 assigns where you place the Amount and Number into the columns of the datatable, followed by the Excel Scope with Write Range.
Assign row(“Amount Posted”) = CFR_Amount_From_MM
Assign row(“Confirmation Number”) = CFR_Num_From_MM
Excel Scope
Write Range
“Journal” “A1”
mydata
Thanks.
OK, that makes sense! I was thinking AmountPosted = row(“Amount Posted”).ToString was defining the range of row Amount Posted to the variable AmountPosted (thanks for clarification!).
Thank you for not taking me down the complex path lol, I am confused enough!
OK - so I created the 2 assigns as you told me (shown below)
I also put in the range like you told me…(Peek inside the “Excel application scope”)
This is now throwing me the below error…
Ok, so that could be a problem with UiPath. Check your Packages to see if there’s any updates to Excel Package.
We were getting Range does not exist also on our end which UiPath was trying to fix last I heard.
An alternate route is to use “Write CSV” instead of Excel Scope but will not be formatted.
If Write Cell works with hardcoded ranges like “G2” etc, let me know I can try and provide the way to turn the datatable range into the letter range for you.
Hi @ClaytonM,
Just checked, there are no packages available for update and Write Cell is working fine with the hardcoded ranges.
Ok here is the complicated alternative where you take the Column Index and Row Index and convert it to a Range to be used in Write Cell. This is not tested completely, however, since I’m lazy, lol. I would recommend using a Message Box to output the Range before the Write Cell to make sure it’s correct.
Create a variable to store the range like strRange.
Assign strRange = If(Number<=26,Convert.ToChar((mydata.Columns.IndexOf(“Amount Posted”) + 1) + 64),Convert.ToChar(CInt(Int(((mydata.Columns.IndexOf(“Amount Posted”) + 1) - .001) / 26) + 64)) + Convert.ToChar(If((mydata.Columns.IndexOf(“Amount Posted”) + 1) mod 26 = 0,26,(mydata.Columns.IndexOf(“Amount Posted”) + 1) mod 26) + 64)) + (mydata.Rows.IndexOf(row) + 2).ToString
Write Cell using strRange for the Range field
And copy that Assign for Confirmation Number but replace “Amount Posted” with the correct column name.
strRange should be equal to like “G2” or whatever the correct range is.
Thanks. If there are errors I can help resolve.
Wow - thanks for this @ClaytonM! You have been very helpful!
I was able to use the below link to help as well!!! 
Hi @ClaytonM,
I am a little confused. If I am going to use the datatable method, where do I put the assign n = n+1?
I tried to put a list of string in a col in different rows. e.g. {1,2,3,4,5} into column A
A
1
2
3
4
5
I have created a really simple xaml as an example. Could you help? Thanks
a.xaml (15.4 KB)
Lavina
and I keep getting this error…
ashokkarale
anjani_priya
Dheerendra_vishwakarma
Parvathy
Aakash_Singh_Rawat
Luis_Fernando
bjorn2390
neco
pere
Shiva_Nikhil