How to read value from a single cell but write the same value in an entire range?

Help
1

I need to read invoices from multiple sheets. Each sheet has a contract number.

I want to write all the invoices to a single sheet, in a table. But I need to write the “foreign key”, that is, to what contract each invoice belongs to.

Thus, I have acquired the contract value and stored it in VarContractNum.

I have found and used ReadRange for the invoices from that contract sheet (let’s say B35:J50), stored in VarDatabase

Now I use a write range, “TableInvoices”, starting at B2, source VarDatabase.

Everything fine up to this point.

But now, I want to write, from A2 to A17 the same contract number, showing that all transfered invoices from B35:J50 of sheet contract 5050 belong to contract 5050.

It seems WriteRange and AutoFill Range all depend on using a DataBase variable, and contract number was stored at VarContractNum.

Anyway to paste a single value from a single variable to a range, or will I have to slowly write each line using a counter?

2

if you know that you will need the same value from a2 to a17, you can create a datatable of that size putting the same value in all cells and then use write range…

3

Hi @Rogerio_Penna,

Use the build datatable if you still don’t have a datatable. Then use the Add Data Row activity to add a row to the table at the end just use write range to write the table to the workbook/excel.

4

the number of rows that will have the same value change from sheet to sheet. (contract to contract)

it may be 15 in one contract, 2 in the other, 30 in the next…

5

ok, it seems I need to learn how to build databases at UiPath…

6

All you need to know is at: academy.uipath.com
Also I think there are a few youtube videos out there about datatables.

7

The Build Data Table activity has a very nice “Wizard”, will be easy :slight_smile:

8

Yes, I tested it. Like a graphical wizard to add and delete columns, type of columns, etc.

What I actually need to understand is how to copy the same value x number of times to the rows.

9

Use Add Data Row inside a loop, populate the ArrayRow property like this: {“value”,“value”,“value”}

10

So I have to use a loop to populated the table with several rows.

But is it faster to use a loop, populate a datatable rows with the same number, then paste the datatable to Excel, or would it be faster to use the same loop to populated DIRECTLY the Excel table?

11

In my experience is faster for the robot to perform only one write operation in excel than multiple write cells ones, also will be faster to code too…

1 Like