Open excel files

[office_work]

I want to open multiple excel file.
In all excel file there is value in column A as staff name and in any cell of column A or B Or C there is total amount is written
i want to copy the that entire row and paste it to the new excel sheet.

I tried to create flow
excel_Automation.xaml (10.9 KB)
But still getting error and not able to perform the desired task.

[AJ_Ask]

Hi @office_work

Use For each file in a folder for iterating through files in that folder. Also use Fiter for xlsx files.

Then Use Excel File with CurrentFile.FullName as path which will take your Excel file as Path, Inside that you read range which will store the Data in datatable.

Then you can use Excel application scope for pasting the data into the new excel sheet. Use write range if only one excel sheet, if you are adding data from multiple sheets use append range.

Attachment for your ref
Excel_auto.zip (92.9 KB)
Hope this helps

[copy_writes (chethan)]

@office_work

I want to open multiple excel file.
In all excel file there is value in column A as staff name and in any cell of column A or B Or C there is total amount is written
i want to copy the that entire row and paste it to the new excel sheet.

• Excel Application Scope (for opening each Excel file)
  • Read Range (read the data into a DataTable)
  • For Each Row (inside the DataTable)
    • If (check for staff name and total amount)
      • Add Data Row (add the row to a new DataTable)
• Excel Application Scope (for creating a new Excel file)
  • Write Range (write the new DataTable to the new Excel file)

[postwick (Paul)]

You don’t open the files and copy/paste. You use the Excel activities.

DataTables and Excel Automation with Studio (v2020.10)

[Yogeesh_G]

Hi

->. In the Excel Application Scope activity, drag a For Each activity.
->. In the For Each activity, set the Collection property to a collection of the Excel file paths. You can use a Get Files activity to get a list of file paths, or you can manually enter the file paths in a string variable.
->. Inside the For Each activity, drag an Excel Application Scope activity.
->. In the Excel Application Scope activity, drag an Open Workbook activity.
->. In the Open Workbook activity, set the WorkbookPath property to the current file path.
->. Inside the Excel Application Scope activity, drag a Read Range activity.
->. In the Read Range activity, set the Range property to the range of cells that you want to read. For example, if you want to read the entire worksheet, you can set the Range property to “*”.
->. Inside the Excel Application Scope activity, drag an If activity.
->. In the If activity, set the Condition property to a condition that checks if there is a value in column A or B or C that matches the staff name you are looking for. For example, if you are looking for the staff name “John Doe”, you could set the Condition property to the following expression:

Row("Staff Name").ToString.Trim = "John Doe" Or Row("Total Amount").ToString.Trim <> ""

->. Inside the If activity, drag a Copy Range activity.
->. In the Copy Range activity, set the Range property to the current row.
->. Inside the If activity, drag an Append Range activity.
->. In the Append Range activity, set the Range property to the range of cells in the new Excel sheet where you want to paste the copied row.

Thank you

[Gayathri_Mk]

For Each Excel File in Directory.GetFiles(“YourFolderPath”, “*.xlsx”)
- Excel Application Scope (File: Excel File)
- Read Range (Output: DataTable)
- For Each Row in DataTable
- If (Condition for Staff Name and Total Amount)
- Copy Data Row
- Excel Application Scope (File: New Excel File)
- Write Range (Write the copied data)