Hi,
I was trying to write data in every row of Sheet1 to Sheet2 on the same file.
It looks like I’m missing something.
Can anyone help with this?
Thanks!
Just use Write Range. Give it the datatable and it automatically writes the rows to whatever Sheet you tell it. It’ll even create the Sheet if it doesn’t exist already. You’re making it more complicated than it needs to be.
Thank for looking to it.
I’m more interested and wanted to know the expression if I have to use the above method. Especially in those two fields (cell and cell content).
Thanks
Hi @Sisay_Dinku
You want to write whole data from Sheet1 to Sheet2, If my understanding is correct then no need to use for each row in datatable and write cell workbook activity, Follow the below steps,
By using Workbook activities,
→ Use Read Range workbook activity to read the excel in Sheet1 and store the data into a datatable called dt.
→ Then Use Write range workbook activity to write the dt into the Sheet2, In file name give the same file name and give the sheet name as Sheet2.
Check the below workflow for better understanding,
By using Excel activities,
→ Use the Use excel file activity and insert the Copy\Paste range activity inside of it.
→ In Copy/Paste range activity, In Source field give Excel.Sheet(“Sheet1”) to copy data from Sheet1
→ In Destination field give Excel.Sheet(“Sheet2”) to paste data to Sheet2.
→ Select All and Include header in dropdowns.
Check the below workflow for better understanding,
Hope it helps!!
Cell would be something like “A1” or “B5” or whatever. Same as cell references in Excel.
The “Cell content” is what you want to write to the cell, ie “this is my value” or a variable containing the value.
Hi @mkankatala
Both your suggestion and suggestion by @postwick work for the simple process writing from sheet1 to sheet2 in the same file. I should have asked exactly what I wanted to accomplish -
However, in my current process I use Reframework and QueueItems as transaction items - after every transaction is processed, I want the whole data in the current row of sheet 1 to be copied to the current row of sheet2.
The reason I wanted to use write cell under for each row in data table in the example I posted is to have understand how to handle when I use REF. My apologies for the confusion –
So, can you give an example how to write back every data in the current row (for all columns) of sheet1 to sheet2’s current row?
Please let me know if I have to create another topic for this -
Thanks for your help!
In that case just use Append Range instead of Write Range. It’ll automatically write to whatever is the next available row in Sheet2. Just give it a datatable with only the one row you want to write.
If you really want to explicitly write to Sheet2 based on wherever your process is in Sheet1, you’ll need to be using modern with For Each Excel Row in Sheet1 so you will then have the reference to whatever row/cell you’re reading and can use that in the Write Cell activity (which should also be modern).
Doing what you’re asking, with classic/workbook activities, is going to be very difficult.
Here is how you do it.
Source file: Courts key.xlsx (9.5 KB)
Code:
Full Log Message expression is CurrentRow.ByField("TEXAS COURT").Address
Full Write Cell expression is Excel.Sheet("Sheet2").Cell(CurrentRow.ByField("TEXAS COURT").Address)
Output:
Result in file:
If you’re working off of queue items, there is no sheet 1. There’s just the values in the queue item. Queue items don’t have a reference to the Excel file they came from. The only way to do that would be to have your dispatcher automation intentionally write the cell address to the queue item as one of the queue item values.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.