Get Excel Sheet Names
Hi:
I am trying to connect an Excel file and get all sheet names in the file. When using ADO.Net, the sheet names contain letters "$, or ' " around the sheet name, not getting the exact sheet name. I wonder why. I am also trying to get sheet names by using Excel as a Com object in VB.Net, like:
myExcel =CreateObject("Excel.Application")
myWorkBook=myExcel.WorkBook.Open("my Excel File")
For i = 1 to myWorkBook.Sheets.Count
myTableName(i) = myWorkBook.Sheets.Item(i).ToString
Next
But the code does not work. Does anyone know how?
Thanks.
[598 byte] By [
jiao] at [2007-12-23]
 jiao wrote: |
| Hi: I am also trying to get sheet names by using Excel as a Com object in VB.Net, like: myExcel =CreateObject("Excel.Application")
myWorkBook=myExcel.WorkBook.Open("my Excel File")
For i = 1 to myWorkBook.Sheets.Count
myTableName(i) = myWorkBook.Sheets.Item(i).ToString
Next But the code does not work. Does anyone know how? Thanks.
|
|
When you say "does not work", what do you mean? Does the loop work at all, or are you getting a runtime error when the app tries to instantiate the COM object? If it's the former, try changing your loop to this:
Dim tWorkSheet as Excel.Worksheet
For Each tWorkSheet In myWorkBook.Worksheets
myTableName(i) = tWorkSheet.Name
Next tWorkSheet
I don't have a copy of VB.NET handy at the moment, but I suspect that what Sheet.ToString() returns is "Excel.Worksheet" or something similar. The ToString() method of many objects typically just returns the name or classname of the object.
Okay, that's a good starting point. Does the code I suggested work for you? I don't have a copy of VB.NET on this machine, so I wasn't able to test it, but I think it should do what you need.
jiao, please reply to this thread when you get a chance. I think it's important that you follow up for two reasons:
1.) If the suggestions you receive aren't helpful, I think I speak for most of us when I say that we'd like to know why. I post here solely because I enjoy helping others with programming issues -- I (and most of us here, I suspect) don't get paid for this. If I don't know the answer to a question, I'll either reply with whatever information I do feel confident in providing, or I won't reply at all. If I do answer, it means that I'm fairly certain I can provide a solution (probably because I've had the same problem myself over the years!) 
2.) If the suggestions you receive are helpful, it's important that you check the "Mark As Answer" box. This helps other forums users find solutions to their problems as efficiently as possible.
Happy Coding!
