How to define write range in for each activity

[YoYoZZZ (Zhu Yo Yo)]

When i try to extract the excel file name in a folder and write that excel file names in an excel file, the write range will always start from row 1 , but i need to write to the first nonblank row, as the file list may be updated.

output for “for each” activity was set as variable :index

write cell properties

[Nguyen_Van_Luong1 (Nguyen Van Luong)]

Hi @YoYoZZZ ,
You need find 1st blank row?
You can use read range to read all file
use for each row check if row empty - return index
regards,

[AJ_Ask]

Hi @YoYoZZZ

You got the excel file name lets assume it is “TestExcel123”.
We will use Excle Process Scope & Use excel file to read the excel & read range to store the output in Dt_Main variable

After that we will use Add Data Row where we will mention the variable where we store the excel file name in this case “ExcelFileName”

Hope this helps

Then we will use write range to overright the data with the new row.

[ppr (Peter Preuss)]

@YoYoZZZ

but i need to write to the first nonblank row, as the file list may be updated.

we would use the append range activity when data is already present within the excel

[sanjay3 (Sanjay)]

Hi @YoYoZZZ

Here this you can try

1. Excel Application Scope
   Read Range (
   Output to DataTable: dt

Create a int variable of firstEmptyRow

2. Assign firstEmptyRow = dt.Rows.Count + 1

3. For Each item in Directory.GetFiles(FolderPath, “*.xlsx”) // To get Excel files only

    Write Cell
    SheetName: YourSheetName
    Cell: "A" + firstEmptyRow.ToString
    Value: Path.GetFileName(item)
   

   Assign firstEmptyRow = firstEmptyRow + 1

Hope this helps