How to read the different sheet names in excel

[sundeeperanti (Sundeep kumar)]

hello,

im trying to read the sheet names in excel

[Niket_Ghai]

Do you want to read the Data inside different sheets or you want the names of the sheets?

[sundeeperanti (Sundeep kumar)]

names of the sheets and data of the sheets aslo

[Niket_Ghai]

Get Sheet name in the case there is only 1 sheet

Hi, first of all, I have already read the topic How to get the Sheet name from Excel . What I want to know is simple, even if I have only 1 sheet in my excel, should I use the IEnumerable type and For Each Loop? I have a excel file with 1 sheet and I want to Read Range from it. It means I should set the sheet name in Read Range activity. Thanks.

This might help! Also can you tell me what are names of sheets?

[Rishabh_Lakhera]

Hello @sundeeperanti

Check out this workfow i made for you
Just change the file path and run
SheetNames.xaml (8.8 KB)

Remove ‘Read Range’ if you only want names

[sundeeperanti (Sundeep kumar)]

thanks @Rishabh_Lakhera

[Naoneko (Naoko Sumida)]

Yes!
I do not want to convert all worksheets to pdf files.
I want to check data inside every worksheets and convert some worksheets.

[Faraz_Subhani (Faraz)]

Hi @Rishabh_Lakhera
I tried your solution, works fine while extracting the sheet name but further when i am trying to read the text for that sheet, i get the below error, please can you assist.

[Rishabh_Lakhera]

Are you trying to add a column?
Can you show me your workflow?
How are you extracting the value?

[Faraz_Subhani (Faraz)]

Hi,
Below is my workflow for Excel application scope,If it is not clear i will create a similar one and attach it.
I am not trying to add any column, i just want to read the data for each sheet name, so i have put the Read range in the for loop.
.
The error comes in Read range.

2nd doubt i have is that why the excel application scope is opening the xcel file in background, what if i don’t want it to be opened in background!

[Niket_Ghai]

Hi!
If you don’t want the excel to work in background you can tick the visible property!
The error in read range is because you are using the same data table again and again for every sheet!
You will need to set it as new DataTable using an assign activity inside the sheetname for loop!

I hope this works

[Niket_Ghai]

SheetNames (1).xaml (9.7 KB)

I have made the necessary changes!

[Rishabh_Lakhera]

Change the type of “for each” activity from object to string from the properties pane!

As for the second question, uncheck the “visible” property from the properties pane of “excel scope application” activity!

This should work

[Faraz_Subhani (Faraz)]

HI @Niket_Ghai @Rishabh_Lakhera,
I am still getting the same error,

I am using the variable ExcelText and assigning new table to it and iterating for every new sheet name, still i get the same error and i also tried changing the type of “for each” activity from object to string.
May be i am doing some stupidity:-(

[Rishabh_Lakhera]

Try ExcelText = New System.Data.DataTable

[Rishabh_Lakhera]

Could you attach the xaml file please?

[Niket_Ghai]

SheetNames (1).xaml (10.4 KB)

Working for me
Try this workflow and check if this also gives the same error

[Faraz_Subhani (Faraz)]

Thanks @Niket_Ghai and @Rishabh_Lakhera.for your prompt replies.
The solution works perfectly for .xls and .xlsm files but i get same error for .xlsx files
.I hope the excel application scope is applicable for .xls,.xlsx as well as .xlsm files to.
Also, the bot by default reads the hidden sheet names as well, is there a way i can tell the bot not to read the hidden sheets?

[Niket_Ghai]

What error does it gives when working with .xlsx files?

[Faraz_Subhani (Faraz)]

Same, as i was getting earlier

but i t works fine with .xls and .xlsm.