Insert/delete rows in excel based on database row count

Hello all,
I am trying to loop in a ‘insert rows’ excel activity at a set point (row 10 for example). How many rows to insert depends on how many rows in my filteredDT. 5 rows in DT should be 5 rows inserted at row 10 in excel sheet 1. Please help.

  • Filter data table (Input: DT1, Output: DT2)
  • Excel application scope
    • For each: row in DT2
    • Insert/Delete rows (“sheet1”, NoRows:1, Position:10)

@D_Tee

Set no. of rows as below.

              inputDT.Rows.Count
2 Likes

Good afternoon @D_Tee,

It sounds like you may also be able to use the Insert Rows activity without the For Each loop. In your NoRows argument, try DT2.Rows.Count

(Oops, Lakshman beat me to it!)

1 Like

@chenderson

Hehe

In your application scope, use the ExcelInsertDeleteRows activity to insert the number of rows from your datatable (e.g. if your table is DtData, then pass DtData.Count to the NoRows parameter of the activity, and set Position to row 10 if that’s where you want to make the insertion).

The aforementioned step inserts blank rows above the input Position. Next, write the data to the table starting with cell “A10” using the ExcelWriteRange activity. This will not overwrite your data, since you’ve inserted enough blank rows to make room for the inserted data.

And adding to @lakshman’s answer, if you want to update the excel always at the end of the page then use append range activities.
if you just want to write the same filtered data directly to excel you can use select query on Top of DT2(output) to choose only those columns which you need to write and make it as copy to data table and write that temporary data table to excel.

1 Like

Thank you all very much! This solves my issue. Great community on these forums I must say!

1 Like

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