An error with worksheet that contain space

Question

0.00/5 (No votes)

See more:

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

Posted 13-Feb-14 3:33am

Mohamad M. Mohamad

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pheonyx · Accepted Answer · 2014-02-13T03:45:00

Solution 1

That error means "Bad index" (see this link[^])

If you don't know what the name of the sheet is, just that it will always be the first sheet you could do:

xlBook.Sheets(1)

another option is if the user inputs the name of the sheet you could iterate around each sheet comparing a trimmed version of the name. The .Trim() function can be used to remove all leading and trailing whitespace characters from a string. This wouldn't be very efficient however as it does need to go around each sheet.

A third option, would be to get a list of sheet names and ask the user which one they wish to access.

Posted 13-Feb-14 3:45am

Pheonyx

Comments

[no name] 13-Feb-14 10:00am

Can't use index since its not fixed I need to get worksheet by name.

[no name] 13-Feb-14 10:01am

Where I can trim? the space is from the excel side???

Pheonyx 13-Feb-14 10:09am

The space would be in the sheet name by who ever created the excel file.. so if you would probably need to use some sort of iteration.. loop through each sheet... read the sheet name, trim it and see if it matches the one you are looking for.

Sudo code:

foreach(var s in xlbook.sheets)
{
if(s.sheetname.trim() == "sheet1"
{
sheet = s
break out of loop
}
}

[no name] 13-Feb-14 10:18am

Nice piece of code. if you like my question upvote it and post this as answer

[no name] 13-Feb-14 10:02am

If you like my question upvote it.