How to read sheet name of an excel file

Help
,
6

I assumed that. Not seeing you much around these days, seem to be busy?

7

@vvaidya Yeah kind of trying new things and workload… :frowning:

What about you?..

8

Work Load will begin from May (a lot) so will be in your shoes then.

9

haha … now just trying something nasty :stuck_out_tongue: So :smiley: Lets see what future is holding with it for me lol :slight_smile:

10

i need to get the hidden sheet names in the excel workbook…what should i do

11

Hey @niridi

You can also get the hidden sheet name as well form Excel Workbook Object.

Test.xlsx (10.9 KB)

hidden sheet.xaml (6.9 KB)

IN the above excel Sheet2 and Sheet6 are hidden.
when you will run the sample still u will be able to get those sheet name with other visible sheet name as well.

Regards…!!
Aksh

1 Like
12

its working good :slight_smile:

thanks!!!

13

i want to get only the hidden sheet name instead of getting all the sheets in the workbook

1 Like
14

The example you provided is not working. I’m getting following error: : Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.Office.Interop.Excel.Worksheet’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{000208D8-0000-0000-C000-000000000046}’ failed due to the following error: En sådan grænseflade understøttes ikke (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

What is the solution of getting all sheets names within a workbook?

15

@aksh1yadav do you happen to know how we can get around this issue posted by @Ohrid?
I believe, in my case, it is because the Excel file has a Chart on a sheet.

Excel Package Version 2.3.6682.26635
I tried reinstalling the Excel package and am on the most recent.
I have tried multiple machines and with admin rights.

I am using wb.GetSheets() when the error occurs.

If there isn’t a known solve for this, I will escalate it to UiPath Support.

Regards.

C

16

Hi @ClaytonM,
Can you try this “WorkBook.GetSheets.ToList()” .

Below It has the example

Regards
Balamurugan

1 Like
17

Thanks, adding .ToList didn’t help.
It only happens when there is a Chart as one of the sheets.

18

Hi @ClaytonM,
If possible , can you share the source.

Regards
Balamurugan

19

I reproduced issue with a new file:

image

Excel file with Chart on a New Sheet: test1.xlsx (11.3 KB)

Thanks.

20

Hi @ClaytonM,
Yes. You are right. It is giving error only to have the chart. It doesn’t have cells only the chart . I copied chart into another excel file. And tried to get the sheets count . It is working.

image
image727×377 9.11 KB

Regards
Balamurugan

21

Hi,
I tried to read the excel cell using read cell activity and that cell contain formula ,the data is of the month number suppose for April is 04but while reading it read as 00 instead of 04.can anyone have any idea regarding this issue.

22

True, if the Chart is inside a sheet then it works fine. However, if you move that chart to its own sheet within the file then the problem occurs. I’m trying to avoid solutions that force users to use a file in a specific way.

I’m going to just have the user remove the Chart or keep it inside a sheet, until there’s an ideal fix that doesn’t require workarounds.

Thanks.

23

Hey @ClaytonM

Sorry for Delayed Response :slight_smile:

Well it is due to chart object.
So There are you can use chart inside an excel either in an existing sheet by embedding them or by moving the chart object to new location or sheet. by Moving a chart to new sheet or location either you can assign your sheet name or default naming will be like chart1,chart2…and so on.

Here with Excel Application scope -the Worksheets interface only gives you worksheets - not charts.

The error message which you guys are getting is due to cast is wrong..

_Workbook.Sheets Returns a Sheets instance. mentioned interface gives us all types of sheets, not just worksheets; like charts, macro sheets, etc

On the other side , the Worksheets interface only gives you worksheets - not charts.

Reference Post - Error: 'Microsoft.Office.Interop.Excel.Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D8-0000-0000-C000-000000000046}' failed - #4 by aksh1yadav

Regards…!!
Aksh

24

So what you are saying is… I need to use Invoke Code?

I was able to get this working using the following:

image
image766×151 8.29 KB

I couldn’t for some reason get the Imports to work but just added the AssemblyReference in the .xaml file

Then I needed to Cast the Sheets to pull in only the Names as an array.

Any further advice is appreciated.

Thanks.
@Ohrid @balupad14

25

Using invoke code you will be creating another instance of the same spreadsheet.

Another idea is to Invoke VBA inside excel application scope, but you need to create a vbs file to Invoke the code and save it in a location. Your call.

image

image

Function Sheets()

    Dim sheet As Worksheet,chart As Chart,s as string
    
   'Get Sheet Names
    For Each sheet In Worksheets
    	s = s & sheet.Name & ","
    Next
 
    'Get Chart Names (Remove this if not needed)
    For Each chart In Charts
    	s = s & chart.Name & ","
    Next
    
    If (Right(s, 1)) = "," Then
    s = Left(s, Len(s) - 1)
    End if

    Sheets=s

End Function
1 Like