How to read the different sheet names in excel

Help
,
9

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.
image

10

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

11

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.
image.
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!

12

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!

visible
visible891×192 8.12 KB

datatble
I hope this works :slight_smile:

13

SheetNames (1).xaml (9.7 KB)

I have made the necessary changes!

14

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 :slight_smile:

15

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

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:-(

16

Try ExcelText = New System.Data.DataTable

17

Could you attach the xaml file please?

18

SheetNames (1).xaml (10.4 KB)

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

19

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?

20

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

21

Same, as i was getting earlier
image
but i t works fine with .xls and .xlsm.

22

That is weird because it works perfectly fine for me when using .xlsx files! :thinking::thinking:

23

Apologies!
It works fine for .xlsx and .xlsm, error comes for .xls file
Sorry for bothering.

24

Works exactly same for me as it did worked for .xlsx!

image
image1366×768 110 KB

for hidden sheet problem refer to this, might help you :smiley:

1 Like
25

Thanks a lot for sparing your time, there must b some problem with that particular excel file of mine, i will sort it out.

Thanks again :slight_smile:

1 Like
26

You’re welcome mate :wink:

1 Like
27

Hi,

Here is an activity to get all the sheets name

Regards
Balamurugan.S

28