get column name by ADOX

[kalle]

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

 

[GS]

First thing to do is turn on the 'Require Variable Declaration' option
on the 'Editor' tab of the VBE Options dialog. This will cause code
execution to halt as "item1" and "szBookName" are not declared locally
in this sub, and since it doesn't appear to use module/global naming
convention techniques I assume you're letting VBA implicitly decide its
type. If you declared these elsewhere at module or global level then
you should make that apparent in your naming convention:

Global scope: g_szBookName; g_item1
Module scope: m_szBookName; m_item1
This will make your code much easier to be understood by others!<g>

Anything we let VBA do by implicity requires extra processing and so
results in poor code performance when we have lots of that happening
all over the place.<g>

You bothered to both explicitly declare all the other variables so why
not this one?, AND explictly destroyed the objects you created rather
than let VBA do this implicitly!

---
To get the name of Fields(1) into the listbox you have to set the
ColumnCount property to '2'.

The ListBox1 index starts at '0'. That concludes, then, that...

ListBox1.List(0, 0) = tbl.Name
ListBox1.List(0, 1) = tbl.Fields(1).Name

It might be easier to add the table names first and then loop the
listbox to add the field names...

Dim i As Integer
For i = 0 to ListBox1.ListCount - 1
With ListBox1
.List(i, 1) = cat.Tables(.List(i, 0)).Name
End With 'ListBox1
Next 'i

---
Another way...

Dim sTableNames As String, sTableName As String
For Each tbl In cat.Tables
sTableName = Replace(tbl.Name, "$", "")
sTableNames = sTableNames & "|" & sTableName
Next 'tbl

Dim vTableNames
vTableNames = Split(Mid$(sTableNames, 2), "|")
Dim lNumTables As Long, i As Long
lNumTables = UBound(vTableNames)
Dim aListItems(lNumTables, 1)
For i = LBound(vTableNames) To UBound(vTableNames)
aListItems(i, 0) = vTableNames(i)
aListItems(i, 1) = cat.Tables(i + 1).Name
Next 'i
ListBox1.ColumnCount = 2
ListBox1.List = aListItems

HTH

 

[GS]

Change the following part of my previous post as shown below...

---
Another way...

Dim sTableNames As String, sTableName As String
For Each tbl In cat.Tables
sTableName = Replace(tbl.Name, "$", "")
sTableNames = sTableNames & "|" & sTableName
Next 'tbl

Dim vTableNames, aListItems() '//**change1

vTableNames = Split(Mid$(sTableNames, 2), "|")
Dim lNumTables As Long, i As Long
lNumTables = UBound(vTableNames)

ReDim aListItems(lNumTables, 1) '//**change2