CODEWITHSUNDEEP
vb.netexcel
D_Bester
D_Bester

Reputation: 5911

Worksheet names to list(of string)

I'm getting a list of worksheet names in a workbook using VB.net via the Excel Interop. I was looping through all the sheets to get a list of the worksheet names.

But I'm looking for a faster way. I was poking around and found .Cast. That prompted this question. I couldn't find anything on the internet about this possibility.

Imports Excel = Microsoft.Office.Interop.Excel
Function GetWorksheetNames(ByVal wb As Excel.Workbook) As List(Of String)
    Dim lis As List(Of String)
    lis = wb.Worksheets.Cast(Of Excel.Worksheet)().Select(Function(x) x.Name)
Return lis
End Function

Failed with this error: Unable to cast object of type 'WhereSelectEnumerableIterator2[Microsoft.Office.Interop.Excel.Worksheet,System.String]' to type 'System.Collections.Generic.List1[System.String]'.

So how do I get the list of worksheet names using .Cast?

MSDN: Enumerable.Cast(Of TResult) Method

Upvotes: 2

Views: 847

Answers (1)

user2480047
user2480047

Reputation:

Your query is fine, but the declaration of the variable storing the results (lis) is wrong. Note that LINQ methods return Enumerable-like types, which need to be expressly converted into a specific collection type (List(Of String) in this case). Your code can be fixed in the following way:

lis = wb.Worksheets.Cast(Of Excel.Worksheet)().Select(Function(x) x.Name).ToList

Alternatively, you might not perform the conversion to a list at any point and just rely on the type being output by the LINQ query. For example:

Dim lis2 = xlWbSource.Worksheets.Cast(Of Excel.Worksheet)().Select(Function(x) x.Name)

For Each name As String In lis2

    'Use name as you want

Next

Upvotes: 1

Related Questions