Read excel sheet with different name

[Mahesha_Mendis]

Hi friends
i want to read the more than 100 excel sheet with different sheet name
such as
excel1=sheet 1
excel2=sheet2
excel3=sheet3 and so on
how can i do this?
please help me to achieve this

[Lahiru.Fernando]

Hey @Mahesha_Mendis

You can get the file paths of all the excel files in the folder (if they are in the same folder) using

Directory.GetFiles(FolderPath)

This will give you an array of all file paths in that folder…

Now use a for each activity to loop through each file path

Inside the loop, use excel application scope and provide the file path. In that, you can get the worksheet names as well.

Now use another for each loop with

WorkbookVariable.Getsheets

Inside that place your read range activities

[Mahesha_Mendis]

@Lahiru.Fernando can you please give me a sample project

[amithvs (Amyth)]

ArrayOfExcel = Directory.Getfiles(“Your Folder Path”)

For Each file in ArrayOfExcel

  Excel Scope ----> File 1 (give Output Variable to excel scope). Let it be **`Wb`**
  SheetLists = Wb.Getsheets
  For Each Sheet` in  SheetLists
  IF Sheet.Equals("Required Sheet Name")
   THEN do the required process
      ELSE Break

[Mahesha_Mendis]

hi @Lahiru.Fernando
i followed your instruction but when i run it have an error

how can i solve this

[aRani7 (Rani Abburi)]

I think you have any extra spaces in excel sheets, use .trim function

[sunilRdGlobal (Sunil Patel)]

Hello,

Instead of using For each activity you have to use Get WorkBook Sheet activity and get all sheet in string array.
after getting array from activity use this in for each loop.

Thanks,

[Lahiru.Fernando]

Hey @Mahesha_Mendis

In the read range, you have specified the “item” variable as a string as “item”… Remove the double quotes and specify it as

Item.toString and see how it works

[Mahesha_Mendis]

@Lahiru.Fernando
yes i tried that but same error occurred

[Lahiru.Fernando]

What’s the error message that you get? Can you show me the full error?

[Mahesha_Mendis]

@Lahiru.Fernando

[Lahiru.Fernando]

Have you specified the workbook variable here for this property in the Excel Applcation Scope.

You have to use the variable used there in the For Each loop…

[Mahesha_Mendis]

@Lahiru.Fernando
Thanks a Lot
it’s work

[Lahiru.Fernando]

Awesome!!
You are welcome!!