Excel Worksheet name error

▲
1
▼
Dim xl As New Excel.Application
Dim xlBook As Excel.Workbook = xl.Workbooks.Open(myExcelFileName)
Dim xlSheet As Excel.Worksheet = xlBook.Sheets("Sheet1")

So I have the above code that open excel file to perform some operation later, The problem is sme sheets contain space in there names for example "sheetx "

And when trying to read the sheet Dim xlSheet As Excel.Worksheet = xlBook.Sheets("Sheet1")

Its catching an error HRESULT 0x8002000B

vb.net
windows
excel
asked on Stack Overflow Feb 13, 2014 by MohammadMMohammad

2 Answers

▲
0
▼

Maybe your version of Excel doesn't speak English. And "sheet" is a dirty word in the local language, it kinda is in English ;) Your name is a hint that English is not the default language. Use the index instead of the name to avoid accidents like this:

   xlWorkSheet = CType(xlWorkBook.Sheets(1), Excel.Worksheet)

SOURCE

answered on Stack Overflow Feb 13, 2014 by Hoh • edited May 23, 2017 by Community
▲
0
▼

If you really do need to cope with varying names, at varying indexes, then its easy enough to write a function to find the sheet, accomodating variations in the name as you need to.

For example:

Sub test()

    Dim sheet As Excel.Worksheet

    Set sheet = GetSheet("sheet2")

    sheet.Activate

End Sub

'Returns first sheet with a matching name
Function GetSheet(toFind As String) As Excel.Worksheet

    'First parse the name to find
    toFind = ParseName(toFind)

    'Loop through the sheets collection
    For Each sheet In ActiveWorkbook.Sheets

        'Check if the sheet name matches the one we're looking for.
        If ParseName(sheet.name) = toFind Then

            'If match then return
            Set GetSheet = sheet
            Exit For

        End If

    Next sheet

    'Remember to handle no matches: 
    'Either raise an error or return Nothing depending on your requirements

End Function

'Common function for simplifying name for comparison purposes.
'This version allows for spaces in the name, and differences in Upper/Lower casing.
Function ParseName(toParse As String) As String

    toParse = Replace(toParse, " ", "")
    toParse = UCase(toParse)

    ParseName = toParse

End Function
answered on Stack Overflow Feb 13, 2014 by Jon Egerton

User contributions licensed under CC BY-SA 3.0