How to read sheet name of an excel file

[Uma]

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.

[ClaytonM]

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.

[aksh1yadav (AKSHAY YADAV)]

@ClaytonM

I believe, in my case, it is because the Excel file has a Chart on a sheet.

Hey @ClaytonM

Sorry for Delayed Response

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

[ClaytonM]

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

I was able to get this working using the following:

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

[vvaidya (Vinay Vaidya)]

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.

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

[ClaytonM]

Is the Invoke VBA part of the 2018 Studio? I don’t think I’ve used that yet.
And, how does it work to attach to the correct Workbook or did you leave off part of your script, cause normally you need to create an Excel object?

In any case, I placed the Invoke Code before the Excel Scope and turned Visible on, so the Excel Scope attaches to the instance of the file. And, stored all the sheets to an Array so I can dynamically choose the correct sheet name.

Thanks.

[vvaidya (Vinay Vaidya)]

@ClaytonM

Is the Invoke VBA part of the 2018 Studio? I don’t think I’ve used that yet.
And, how does it work to attach to the correct Workbook or did you leave off part of your script, cause normally you need to create an Excel object?

Invoke VBA is part of latest Excel Package and it has to be placed inside Excel application scope. The Vb script will get executed on that specific excel spreadsheet and you don’t have to create the Excel object within your script.