I want to insert data in DataTable into Excel, but not like the way we normally do using Write Range activity. In my Excel file to write to, I already have some pre-filled cells.
… .[ ______ A _____ ] [ _____ B _____] [ _____ C _____ ]
[1] [Date: 03/24/21] [ _____________ ] [ ____________ ]
[2] [ ReplaceData ] [ ReplaceData ] [ ReplaceData ]
[3] [Name: John Xu] [ _____________ ] [ ____________ ]
I hope the above makes sense, but this is how my example Excel looks. Row 1 and row 3 already have some data and these cannot be deleted! The DataTable data need to be inserted at row 2 where it says ReplaceData. The problem is, we don’t know how many rows are going to be in the DataTable. For example, if there are 3 rows in the DataTable, the result has to look like
… .[ ______ A _____ ] [ _____ B _____] [ _____ C _____ ]
[1] [Date: 03/24/21] [ _____________ ] [ ____________ ]
[2] [ DataInserted ] [ DataInserted ] [ DataInserted ]
[3] [ DataInserted ] [ DataInserted ] [ DataInserted ]
[4] [ DataInserted ] [ DataInserted ] [ DataInserted ]
[5] [Name: John Xu] [ _____________ ] [ ____________ ]
As you can see, it pushed down the [Name] row to row 5.
How can I achieve this? By the way, this is a simplified example. In the actual excel we use, there are more rows on the top and on the bottom, which cannot be deleted, and we need to insert, not overwrite, dataTable data in the middle!