Write status to a cell in excel for each TransactionItem using queues

[ChrisPals (Christoffer Palsgaard)]

Hi Community

When the process for a transaction item is done I need to write a status for the item in excel.

Normally I do this this as shown below, but how can i do this simlarly using queues?

I tried TransactionItem.SpecificContent(“Column1”) = “this is my status”, but without luck.

I need the status to be in column ‘N’

Thanks in advance

[ClaytonM]

Hi.

Ok, this is tricky.

I have been uploading my transaction items as json arrays that represent a Data Row, and storing it also to a Data Table that represents the row data. If you have it as a Data Row or Table, then you can use Append Range to update a new “status” file. Also surround this with a Retry Scope, so it can manage multiple robots fighting over the same file.

Otherwise, you must match up the row to certain values of your item, and update the matched row. You could either use the Filter Data Table activity to create a new table of only that specific row, then append it to a new file as mentioned above, or use a .net solution to find the row while maintaining the entire data set. For example ( and this is just pseudocode),

Read Range activity: statusTable
If activity: condition: Not statusTable.Columns.Contains("Column14")
    Add Data Column activity: "Column14"

rowsToUpdate = statusTable.AsEnumerable.Where(Function(r) r.ItemArray.Take(r.ItemArray.Count-1) = {TransactionItem.SpecificContent("Column1").ToString,TransactionItem.SpecificContent("Column2").ToString,TransactionItem.SpecificContent("Column3").ToString,TransactionItem.SpecificContent("Column4").ToString,TransactionItem.SpecificContent("Column5").ToString,TransactionItem.SpecificContent("Column6").ToString,TransactionItem.SpecificContent("Column7").ToString,TransactionItem.SpecificContent("Column8").ToString,TransactionItem.SpecificContent("Column9").ToString,TransactionItem.SpecificContent("Column10").ToString,TransactionItem.SpecificContent("Column11").ToString,TransactionItem.SpecificContent("Column12").ToString,TransactionItem.SpecificContent("Column13").ToString} ).ToArray

If Activity: condition: rowsToUpdate.Count > 0
    Assign activity: rowsToUpdate.Last("Column14") = "new status"
    Write Range activity: statusTable

Again, you might want to surround the Write Range with a Retry Scope to solve for multiple robots accessing the file at the same time. But, solving this where you need to Read then Write, has a split moment where a robot could read in the wrong data, which is why Append Range is more reliable from my perspective right now. But, I would like to solve for this at some point to be able to update an Excel file with formatting, which requires a Read then Write.

Regards.