Read and write to excel for each row

My sequence attempts to open an application which I then try to type text which is contained in column A of an excel file. I then commit the text via clicking a button in the application. I capture the close time of the click activity which I store in a variable which I write to the corresponding row in column B of the same excel file. I try to repeat this by looping through 5 rows of the spreadsheet, incrementing a variable to writer the time to the next row. Currently this only works for the first row and then I get an error For Each Excel Row: Exception from HRESULT: 0x800401A8

I assume this is caused because at the end of each loop I am opening another Excel Application Scope activity to allow me to write the variable stored time back to the excel file which then closes. I have spent a couple of hours trying to work out if there is a way of keeping the excel file open but from discussions on the forum it appears there is not. Could someone please give me some pointers on how I can tackle this. I suspect my approach is not the best way to achieve what I’m looking to achieve.

@kjhsdhkfskh
the general concept to rework with an opened excel on some later part in the flow is to use the output WorkBook:

grafik
the excel application scope activity which opens the excel at the first time sets the output WorkBook to a variable e.g. myWorkBookVar

later we use it on setting Existing WorkBook and let the setting to the path empty

Output

  • Workbook - The entire information from the Excel spreadsheet stored in a >WorkbookApplication variable. This variable can be used in another Excel Application Scope >activity. Only WorkbookApplication variables are supported.
1 Like

Maybe you better want to read the entire sheet to a DataTable, do the magic, then write it back?

1 Like

Thank you both for taking the time to reply :blush:

I have attempted using the DataTable approach. I now read the Excel file to a DataTable and use for each row activity to write the messages to the application and commit via a click activity. As before the data and time the click is made is captured in a variable and converted to a string.
What I am having difficulty doing is writing this value back to the cosponsoring row and column in the excel file. Any guidance would be greatly appreciated. Should I write the value to the datatable and then write the table to the excel file (column 6) or should I write directly the excel file?

Hi @kjhsdhkfskh,

It is faster to write the value to the datatable and then write the table to the file (column 6) than to write it directly the excel file

Best regards,
Marius

1 Like

Thank you @Marius_Puscasu. That amkes perfect sense.

Could you give me some guidance on how write back to the datatabe? I’m having trouble working out how I write to column 6 Row 1 then increment to Row 2 in the next loop through?

Hi @kjhsdhkfskh,

Looping through each row, means that you have a data table, and
All the changes that you are performing are impacting this data table

For writing back the updated datable, you could just simply use the Write Range activity https://docs.uipath.com/activities/docs/excel-write-range

You do not have to write into the excel a single value at a time. By using the write range activity, you are writing in excel the whole data table at once

Hope this helps
Best regards,
Marius

1 Like

Thank you @Marius_Puscasu, That’s really useful.

I have added an assign activity in the loop to try and write the date and time I have stored in a variable as a string to the column 6 of the data table which is titled “Date Time”.

I am getting the following compile error:

Compiler error(s) encountered processing expression “varWhatsAppDataTable(“Date Time”)”.
Option Strict On disallows implicit conversions from ‘String’ to ‘Integer’.

image

The box with the ! against contains the following:

varWhatsAppDataTable(“Date Time”)

Do you know were I am going wrong and how it can be corrected?

Thanks again for all your help.