Update individual cell in Excel Spreadsheet

[craig.norton]

Hi,

I have a source Excel spreadsheet that contains a lot of formulas and formats and I want to update data in a column for a selected account based on some calculations performed in related Datatables. I don’t want to load the spreadsheet via the generic Read Range activity as when I do a Write Range all formatting is lost.

I have a value (account number) saved in a string variable that I want to search for in Column A of the source spreadsheet and then update the string value in Column K for the selected row.

Thanks

Craig…

[Suraj_B_Shetty (Suraj)]

Hi @craig.norton
Check this

Write Cell

[ParasTripathi]

@craig.norton Easy method:

Use Write Cell Activity in workbook or Excel application scope

[arjunshenoy]

Hi @craig.norton

You can use the following query in Invoke Code:

dt.AsEnumerable.ToList.ForEach(Sub(row)
row(“ColumnK_Name”) = if(row(“ColumnA_Name”).ToString.Equals(“AccountNumber”),“Value that needs to be updated”,row(“ColumnK_Name”).ToString)
End Sub)

If you can provide more data, I can redesign the query, Hope this helps.
Best Regards.

[craig.norton]

Hi,

This is a cutdown copy of the Excel Spreadsheet and I want to keep the formatting and formulas. I am analysing other data and end up with an account number (stored as a variable) that needs updating. I want to search the “Acc No” field based upon my variable and when I find a match (there will only be 1 record for a given account) I then want to update the “Send Reminder” column with the text “Send reminder to client”.

[Suraj_B_Shetty (Suraj)]

Hi @craig.norton
Use Look Up Range Activity and pass the account number. It will return the cell address.ie For account number 578850 it will be A2.From the cell adress fetch only number 2 using regex.

Then use write cell activity and pass cell address as “D2”. ie “D”+number obtained from read cell.

[arjunshenoy]

@craig.norton

Give the following query a try:

> io_dt.AsEnumerable.ToList.ForEach(Sub(row) 
> row("Send Reminder") = If(row("Acc No").ToString.Equals(accNo.ToString),"Send reminder to client",row("Send Reminder").ToString)
> End Sub)

Workflow:
Test453.xaml (7.1 KB)

Input:

Output:

Hope this helps,
Best Regards.