How to get multiple sheet name and file name and write in excel

Hello everyone
I have multiple xls file and i want to get each file name with each sheet name and every sheet name should be write in another excel file and read the each sheet and get the value of one specific column and write in same another excel please give me some idea to do this

thanks in advance

Hi @zaid.ahmad ,

Can you try this

GetFiles : Directory.GetFiles(“Your Directory”)

1.Use For each for GetFiles
2.Inside for each use below assign activity to get sheet name

file : New ClosedXML.Excel.XLWorkbook(“Your File Path”) Data type is ClosedXML.Excel.XLWorkbook

sheetName : file.Worksheets data type is ClosedXML.Excel.XLWorksheets

3.Use for each each for sheetName. And data type of for each is ClosedXML.Excel.XLWorksheet
4.Use read range activity to read excel file with the sheetname
5.Write above DT with same sheet name in different file.

Hello @zaid.ahmad ,

  1. Use Build data table to create the DT

  2. use for each file in the folder to loop the files present in a specific folder.

  3. Use excel application scope inside for each file.
    3.1 use the Get Worksheet activity and save the List in variable Sheetlist
    3.2 place the for each activity and loop the list.
    3.3 inside it place the add data row and add the data into it.

  4. The process will Loop

  5. Finally use the write range activity and write the data in to new excel sheets