ADO OpenSchema with Excel and Duplicate table names
Posted on 2003-03-25
I'm currently using ado to get the list of worksheets in a workbook and populating it in a listbox.
The code is as follows:
Dim cn as Connection
Dim rs as RecordSet
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFile.XLS" & _
& ";Extended Properties=Excel 8.0"
Set rs = cn.OpenSchema(adSchemaTables, Array(Empty,Empty, Empty, "Table"))
While Not rs.EOF
Set rs = Nothing
Set cn = Nothing
The problem is that with some excel books i something like the following results:
I looked at the workbook itself and these sheets are only in there once so my questions are as follows:
1) How do I only retrieve the list of actual worksheets in a workbook?
2) Where are the duplicate sheet names coming from? What caused this?
I know that I can of course that I can check to see if the sheet name exists before adding it to the list, but I just sort of curious as to what is going on.