How to Write Range dynamically into next cell? A1>A2>A3

Help
1

Hi Guys,

How to use Write Range activity to dynamically store data in same Excel file, but add new data into the next cell? A1>A2>A3 (horizontally)

FYI, I tried to input into Range field the line below, but it doesn’t work, bringing the error “the range doesn’t exist”

“A” + variable.tostring

image
image936×491 17.9 KB

1 Like
2

Please use Append Range activity. It will append to the next available row.

Regards,
Karthik Byggari

1 Like
3

Declare a variable of type int and increment the value by 1 everytime the loop comes and give the range as

“A” + count.tostring

2 Likes
4

Fine write range activity can be used to write a whole datatable to a excel but the question is like A1>A2>A3
Well that looks like we want to write in each cell one by one
is that so buddy
Cheers @nova001

2 Likes
5

In ExtractDataTable I have only 1 column with several rows. So, when UiPath scraping data from each URL (from URL list) I want it to store first A1,A2,A3,A4 etc. 2nd one to store at B1,B2,B3,B4 etc. and next C1,C2,C3 and so on.

6

AppendRange doesnt work properly:
1 First, it skips 12 rows in Excel by some reason
2 Secondly it copies data 7 times (I guess because there are 7 rows in column and it loops through ForEachRow activity adn copies it 7 times)
3 It copies it vertically, one after each other A1-7, A8-14 etc.

7

@nova001,

You need to use write cell activity instead of that write range in the for each row.

Write range will write the entire data table.

8

For this particular thing, use build data table first, then use add data column when you got the data for the first time.Again another data column for the second URL data and so on. Then use write range activity to write the entire table you build into an excel. This will work. Otherwise, you need to convert the interger to alphabet and use write range activity everytime.

9

I have to scrap lots of URLs/Data… So I wanted to do it in such a way when robot opens first URL from the list > scrap data > store in DataTable(AddDataColumn) > close the browser (repeat same pattern with next URL from the list)

I just want to close the browser every time after each scrap because I am afraid it will freez if it opens too many tabs.

So my question is - do you think it will be possible to do it in such way? Robot will go through say 1000 links, store data in DataTable, then > WriteRange activity to write entire table into Excel.

Thank you for your help guys.

10

Yes, that won’t be a problem. Scrap the data save it in the datatable and close the tab.

If the data is big, use delay of few seconds before and after adding it to data table and to open the new URL

Finally, write data to excel. Excel will save the data , no matter how big it will be

11

Could yopu please help me to put right setting for AddDataColumn? I am not sure how to write the data into DataTable.

and one more question - is the workflow is correct?
Attach browser > BuildDataTable > DataScraping (ExtractStructuredData activity - all saved in ExtractDataTable) > AddDataColumn > ExcelApplicationRange > WriteRange

Q2-screenshot3
Q2-screenshot3928×480 25.5 KB

12

@nova001,

The order should be:

  1. Build data table(remove the existing columns) ex: dt1
    within the loop :
  2. Open browser
  3. Scrape data and it will be a data table
  4. As you are getting only one row, use assign activity and get the first column using dtable.columns(0)
  5. add data column and give the input data table as dt1 and column as the variable above.
  6. Repeat steps from 2 - 5
  7. after the loop exists, Use excel application scope and write range to write dt1.
13

Hi Hareesh,

I tried to do like you advised, but it doesnt work, I need more detailed explanation if possible.
I have attached my workflow, I would appreciate if you can edit it correctly.Nova-DataScrap1.xaml (10.4 KB)

14

Nova-DataScrap1.xaml (10.6 KB)

Here it is and the mistake you did is :

  1. You haven’t created the output of the variable from build data table
  2. Assign activity, you have assigned the data table with the data column
15

Hareesh,

I added 2nd loop and added 7th step as well. It gives me error - “Cannot find Column 0”.
I guess it’s because I emptied DataTable columns. What did I do wrong? Nova-DataScrap2.xaml (19.4 KB)

Q2-screenshot4-error
Q2-screenshot4-error818×535 26.8 KB
Q2-screenshot5-empty%20DataTable
Q2-screenshot5-empty%20DataTable830×560 26.7 KB

16

It is because your extracted table doesn’t have the data

17

Why there is no data? Browser is attached, Data Scraping activity is working as well. It stores it as an output to ExtractDataTable. I tried to replace it with variable for DataTable - dataBuild, but this time it gives me different error, this time for Add Data Column!?

Why it doesnt work?

Q2-screenshot6-error%20AddDataColumn
Q2-screenshot6-error%20AddDataColumn1097×516 28.1 KB
Q2-screenshot7

18

Guys, can anyone help me with this process? cant figure it out for 5 days already!!!
if someone can send corrected completed workflow, it would be much appreciated.

thanks

19

Databuild is the one which you are trying to fill the data from web datatable. Let it be ExtractedDataTable and try to print the rows of data from the datatable you are extracting.

Give the message box before assign as

ExtractedDataTable.Rows.Count

20

Message box gives me - 8 (I guess its 8 rows of data in the column) - so it means data is actually get scraped into ExtractDataTable. Does it get loaded into dataBuild automatically from ExtractDataTable? (its a variable for output in BuildDataTable activitty)