Solved: How to fill all sheet name into Combo in ADO

[domfootwear]

Please show me the way to fill all sheet name into Combo VB6 (Using ADO) ?
Thanks in advance

[Bob Phillips]

Which bit do you need, getting the sheet names, or fill the combobox? And why the SQL forum?

[domfootwear]

Which bit do you need, getting the sheet names, or fill the combobox? And why the SQL forum?

Sorry for my post in SQL forum, please help me to move this topic to another forum.
Yes, I want to fill all sheet names into combobox. I want to use ADO statement.

Thanks so much for your reply.

[Bob Phillips]

Sub GetTables()
Dim oConn As Object 'ADO.Connection
Const sFilename As String = "C:\test\test.xls"
Dim oCat As Object 'ADOX.Catalog
Dim tbl As Object 'ADOX.Table
Dim vecSheets As Variant
Dim iRow As Long
Dim sConnString As String
Dim sTableName As String
Dim cLength As Integer
Dim iTestPos As Integer
Dim iStartpos As Integer

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConnString
Set oCat = CreateObject("ADOX.Catalog")
Set oCat.ActiveConnection = oConn

ReDim vecSheets(1 To 1)
For Each tbl In oCat.Tables
sTableName = tbl.Name
cLength = Len(sTableName)
iTestPos = 0
iStartpos = 1
'Worksheet name with embedded spaces enclosed by single quotes
If Left(sTableName, 1) = "'" And Right(sTableName, 1) = "'" Then
iTestPos = 1
iStartpos = 2
End If
'Worksheet names always end in the "$" character
If Mid$(sTableName, cLength - iTestPos, 1) = "$" Then
iRow = iRow + 1
ReDim Preserve vecSheets(1 To iRow)
vecSheets(iRow) = Mid$(sTableName, iStartpos, cLength - (iStartpos + iTestPos))
End If
Next tbl

oConn.Close
Set oCat = Nothing

With UserForm1

.ListBox1.List = vecSheets
.Show
End With

End Sub

[domfootwear]

Sorry, I made you confused, I want to get all sheet names of an excel file into Visual Basic combo.

Thanks in advance !

[Bob Phillips]

No you didn't, that is what the code does.

[MikeA]

Hi xld,
works great! Is there a way to show the hidden sheets too?
thanks

[hayya123]

Dim Myconnection As Connection
Dim Myrecordset As Recordset
Dim MyWorkbook As String

Set Myconnection = New Connection
Set Myrecordset = New Recordset

'Identify the workbook you are referencing
MyWorkbook = Application.ThisWorkbook.FullName

'Open connection to the workbook
Myconnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & MyWorkbook & ";" & _
"Extended Properties=Excel 8.0;" & _
"Persist Security Info=False"

'Load the selected range into the recordset
Myrecordset.Open "Select Distinct [Market] from [Sheet1$A1:D5000]", Myconnection, adOpenStatic

With ActiveSheet.ComboBox1
.Clear
Do
.AddItem Myrecordset![Market]
Myrecordset.MoveNext
Loop Until Myrecordset.EOF
End With

End Sub