Append range to a specific column

Help
1

Hello !

Rookie here! I’m sorry if this might be something that came up before, but I searched a lot, and either didn’t understand the solution, or tried and it didn’t work.

I have an excel file with columns Sheets: “Data” and “x1”. The “Data” sheet contains multiple columns:
“ID”, “x1”, “x10”,“x100”,“Date”,“time”, while the Sheet “x1” only has 3 columns; “x1”, “x10”, and “x100”. I’m using Uipath to collect the price of items by lots of, 1, 10 and 100 in a game (used citrix OCR method)

The process starts by entering the names of the items I want in an excel file under the column “ID” of sheet “Data”. (I used append range) Whenever a a value is entered in column “ID”; “Date” and “Time” are updated by themselves using a VBA code. Therefore, after this process, only the columns “x1”, “x10” and “x100” are empty. This is the result:

image
image1129×819 15.2 KB

The process then continues by extracting the remaining data with Google’s OCR. I used the generate datatable to automatically separate my data into 3 columns using a separator (the letter K) and outputing in an output data table. After each data is extracted, the data is appended in the second sheet (“x1”).

My issue is I want the OCR extracted data to be appended in the columns of the first Data Sheet. In columns x1, x10 and x100. I tried selecting a range, and then append data, I thought this would append the data to the columns, but it didn’t work.

Is there a way to achieve this? Or copying the data from my second sheet “x1” and append it to the proper data in sheet “Data” ?

EDIT: Forgot to mention that this process will be repeated a lot ! The data will be added in the same way everyday. So I do not want my current data to be overwritten.

Thank you :slight_smile:

1 Like
2

Hi,
As written in the AppendRange activity guide: “Adds the information stored in a DataTable variable to the end of a specified Excel spreadsheet.”

I suppose WriteRange activity is what you should use.

Cheers

1 Like
3

HI @Florian_Prual

Would be glad to help on this… In order to get your problem solved, the Ideal option is to use the Write Range activity. In the Range, specify the columns you want to update.

If you are doing this within a loop, you might need to use a dynamic range value…

I have done something similar to this… What I did is, I appended couple of datatables into a single excel file at two different occasions… This is a simple range parameter which I used to update a column within a loop without messing the existing data

image
image909×444 20.2 KB

Hope it helps!! Let know how it goes for you…

4

Thank you for your reply, this might be what I need. However, the Write Range activity you selected seems to only work with Workbooks, but my file contains macros, therefore the Write Range acitvity from the Workbook activities doesn’t detect my file. When I input the same formula you used in Excel’s Write Range activity, it gives me an error (WriteRow isn’t declared)

image
image1920×1080 164 KB

5

Hi… WriteRow is actually a variable that holds the row number of the excel sheet row. I have actually used it in a loop… So you might need to get that variable created and get it assigned with the row number you need to start write data to…

6

I’m still confused. Now how do I go on and create a variable that holds the row number of the column?
I’ve looked up some solutions in the forum, but something is wrong and I have absolutely no idea what is…
The output of Read Range is data table: LastRow
Assign To: LastRow.Rows.Count.ToString = CountExcel (which is a data table)
But it tells me there is a invalid L-value expression.

image
image433×615 11.6 KB

7

Hi @Florian_Prual

Sorry for my late response… It was night time over here… :slight_smile:

Check the attached workflow. This is how you can achieve it… I have added comments as well for you to understand easily…

ExcelAppendData.xaml (8.9 KB)

Hope it helps!!!

Let know how it works for you…

2 Likes