Accumulating data in excel

excel
activities

#1

Hi,

is there any way to add row by row in each loop data to excel without overwriting previously added data?

I want robot to read value in citrix and if condition is not met, I want it to write the current variable to excel - hence since it is looping through hundreds of values I expect it to write in each loop to different row and not overwrite existing - previously written data - hope it is understandable.

I tried building data table and adding new rows with data and in the end adding data table to excel, but that is not very useful in this specific process as sometimes it stops because of citrix itself and robot needs to start again and loses previously added data in data table. So I would appreciate if you could give me a hint how can robot write new value not to data table but directly to excel each time/loop to new row.

Please help
Thanks


#2

Hi @neplecha,

You can use Write cell activity to write the cell value. Based on index value you can update the value.

Regards,
Arivu


#3

Thanks @arivu96 , you are write, I can use counter to determine cell reference. What if I didn’t know where already existing excel text ended and hence where should start the robot? (I wouldn’t know exact cell)


#4

Hi @neplecha,

I hope you are Loop the datatable . Update the data in the datatable itself, after completing using write range activity overwrite the same sheet.

Regards,
Arivu


#5

Hi,

May I ask a similar question… I want to write to a specific excel row, but do not want to override the data. Can I insert a new row above, and then write into the new row ?

Many thanks !


#6

Hi @Snowman,

Good question

Try this way
In this case use read range activity to get the data table as output

table.Rows.InsertAt(barackRow, 1)

You can add the data row in-between the table.

After that use Write range activity to append the data.

Reference

Regards,
Arivu


#7

Hi Arivu,

Many thanks for your prompt advice. Would you mind giving further instruction on this : )

Assuming I need to insert a new row at row 12 to the DataTable named DT, should I use Assign activity to:
DT = DT.Rows.InsertAt(12,1) ?
then write data to this row, and use Write Range to write back the whole datatable back to the excel worksheet ?

Regards,
Snowman


#8

Hi @Snowman,

InsertAt(your new datarow, index position)

Yes correct.

Regards,
Arivu


#9

If I want a blank row, can I put {} as your new datarow ? Just don’t know what to put in the your new datarow …

InsertAt(your new datarow, index position)


#10

Hi @Snowman,

Create datarow object and pass there.

Regards,
Arivu


#11

Sorry to bother you again, what would be the correct syntax ?


#12

Hi @Snowman,

Using Invoke Method

in TargetObject — dt.Rows

in MethodName - InsertAt
In Parameters of Invoke Method

In ->DataRow — pass your dr variable
in->Int32 — your index position

Regards,
Arivu


#13

:sweat_smile:
did I miss anything ?

image


#14

Can you send the screenshot of the parameter for the invoke method

Did you created datarow object??

I think you passed wrong parameters


#15

Is this ok for creating the Datarow ?image


#16

Hi @Snowman,
f2f266e4b3cc3a37bb16020b81272bac1f2c978d_1_637x500

Kindly send me screenshot like above image

Regards,
Arivu


#17

Does the excel file matter ?

Best Regards,
Snowman


#18

I told to pass 2 in arguments

You passed only one that too wrong

Firat In argument ->DataRow — pass your dr variable
Second in argument->Int32 — your index position


#19

Got it !! My fault… my first time using Invoke Method.
It works !!

Thanks so much for your patience.:pray::pray:


#20

Great @Snowman