Writing data in Excel ForEachRow

Hi All!

Having an issue during my ForEachRow activity. I have successfully captured the text I want via GetText (I used a message box to confirm and each time it looped a new message box with the info popped up) and assigned it a variable. What I now want to do is input that data into the same row but in a different column.

So right now I have:
Excel Scope
Read Range
ForEachRow
AttachToWindow (for the app I need to use)
TypeInto (where Im using row(0).ToString)
SendHotkey (to start query)
GetTexts (9 total)
And then I run into my issue of not having a way to export those variable from the the GetText back into the excel file.

I tried using WriteCell using “Sheets1” - row(2) and then the variable for the input. It kept throwing an error “Option Strict On disallows implicit conversions from ‘Object’ to ‘String’” so I tried row(2).ToString, still no effect.

Thanks in advance!

1 Like

Fine
we can handle this with either write cell writing directly to the excel or by writing it to the datatable first and then writing that whole datatable back to the excel
–so for first method
in write cell
–for sheetname property - mention the sheetname between double quotes
–for cell range property - mention like this
"C" + (yourdatatablename.Rows.IndexOf(row)+2).ToString

where C is the column i have chosen and you can mention as per your excel either as A or B or E…
and finally
–for value mention the appropriate GET TEXT output variable of type string as input

or

if we go for second method of writing to a datatatble and then writing it to a excel then
inside the for each row loop use a assign activity and mention like ths
row(“yourcolumnname”) = gettextvariablename.ToString

simple isnt it
hope this would help you
Cheers @heblightning

1 Like

@Palaniyappan once I have the assign activity do I need to do a write range so it will save? When I open the file it has not been updated. I am extremely new thanks for the patience.

@heblightning,

You can build one datatable by using builddatatalbe activity for only the values you want to capture and once you captured all the values then you can write it to the excel sheet by using writerange activity.

Thanks,
Sid

1 Like

@siddu509 I tried the method you mentioned and sent it to a new excel file. The new file is blank, not even the old info is in there. I’m not sure what I’m doing wrong. I did manage to figure out the solution though. I just needed to add a writerange in the foreachrow after I assigned the row to the variable as @Palaniyappan mentioned in his second solution.

Thank you both!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.