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:
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.
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?
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?
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
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’.
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?