Get Excel Sheet Names  
Author Message
jiao





PostPosted: Visual Basic for Applications (VBA), Get Excel Sheet Names Top

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.





Microsoft ISV Community Center Forums2  
 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), Get Excel Sheet Names Top

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.



 
 
jiao





PostPosted: Visual Basic for Applications (VBA), Get Excel Sheet Names Top

, 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.

This is the problem I mean "It does not work". It gives the class name not the sheet name.

Thanks.


 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), Get Excel Sheet Names Top

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.



 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), Get Excel Sheet Names Top

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!



 
 
jiao





PostPosted: Visual Basic for Applications (VBA), Get Excel Sheet Names Top

It works. Thanks a lot.
 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), Get Excel Sheet Names Top

焦先生,

Glad I was able to help.

~ duck thing



 
 
aem1988





PostPosted: Visual Basic for Applications (VBA), Get Excel Sheet Names Top

good day sir, can you give me the declarations that you use in "myExcel" and "myWorkbook" because i really really need it... i can't able to run my program... i just follow the steps that you take...


here is my code..


Dim myExcel As Object
Dim myWorkBook As Excel.Workbook
Dim tworksheet As Excel.Worksheet

myExcel = CreateObject("Excel.Application")
myWorkBook = myExcel.Workbook.Open("C:\sample.xls")

For Each tworksheet In myWorkBook.Worksheets
Me.ListBox1.Items.Add(tworksheet.Name)
Next

hope you could help me... thaks and godbless....