How to add a row to a datatable in Excel Automation Scope

Continuing the discussion from How more effectively add row to the Excel:
Hi,
what simple thing am I missing when trying to use the add data row activity?
See the attached xaml file.
Excel_AddRow.zip (20.2 KB)

Hi Christoph,

You need to write the whole range again into your excel. Add data row just adds a row to the data table you extracted (as a variable). You need to write it again into the excel. Use Write range for that

2 Likes

Thanks, it works!

You have two ways to write the updated data table back to the excel file.

  1. Write Range
    Overwrites the existing data from the selected cell (in this example, ā€œA1ā€ thereby replacing the entire worksheet)
    Since youā€™re using ā€œRead Rangeā€ before adding a new row to the same datatable, all the existing data will already be in this table. Write Range will rewrite the whole sheet, thereby giving you the ideal outcome of having old data as well as the new rows.
    Keep in mind the number of columns, though.
    The first row has 6 columns (#4 and #5 blank but column 6 has the value ā€˜Test-03ā€™ in it)
    If you add a row with just 3 values, that may omit the remaining columns, check the behaviour and change your logic accordingly.
  2. Append Range
    You may choose to skip the ā€œRead Rangeā€ activity altogether and use ā€˜Append Rangeā€™ directly to the worksheet of your choice. This will automatically find the next blank row and append your new row to the existing data.
    In this workflow (attached below) I have used ā€˜Append Rangeā€™ but while keeping the original ā€˜Read Rangeā€™ call intact, so the entire table will be repeated. If you remove ā€˜Read Rangeā€™ activity and simply add a row and then append range, you should get the desired outcome.

Main.xaml (10.3 KB)
Have a play with these activities and decide what you prefer. :slight_smile:

1 Like

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