Please show me the way to fill all sheet name into Combo VB6 (Using ADO) ?
Thanks in advance
Please show me the way to fill all sheet name into Combo VB6 (Using ADO) ?
Thanks in advance
Which bit do you need, getting the sheet names, or fill the combobox? And why the SQL forum?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Sorry for my post in SQL forum, please help me to move this topic to another forum.Originally Posted by xld
Yes, I want to fill all sheet names into combobox. I want to use ADO statement.
Thanks so much for your reply.
[vba]
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
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Sorry, I made you confused, I want to get all sheet names of an excel file into Visual Basic combo.
Thanks in advance !
No you didn't, that is what the code does.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hi xld,
works great! Is there a way to show the hidden sheets too?
thanks
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$A15000]", Myconnection, adOpenStatic
With ActiveSheet.ComboBox1
.Clear
Do
.AddItem Myrecordset![Market]
Myrecordset.MoveNext
Loop Until Myrecordset.EOF
End With
End Sub