Good day Group,

Uses the code below to get the worksheet names from a closed workbook,
How to modify the code to also get the column name of the first column
into the listbox? Grateful for help,

Brgds

CG Rosen


Sub GetSheetNames()

Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim lRow As Long
Dim szConnect As String
Dim szTableName As String
Dim Columns As ADOX.Columns

szBookName = sConString1

If Val(Application.Version) < 12 Then

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sConString & ";" & _
"Extended Properties=Excel 8.0;"
Else

szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & sConString1 & ";" & _
"Extended Properties=Excel 12.0 Xml;"
End If

Set cnn = New ADODB.Connection
cnn.Open szConnect
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cnn

UserForm1.ListBox1.Clear

For Each tbl In cat.Tables

szTableName = tbl.Name
If Right(szTableName, 1) = "$" Then
item1 = Left(szTableName, Len(szTableName) - 1)
UserForm1.ListBox1.AddItem item1
?????????????????????????????????????????????

End If

Next tbl

cnn.Close
Set cat = Nothing
Set cnn = Nothing

End Sub