getting sheets when using ADOX Catalog to read Excel

[pietlinden]

I have a really dumb question...

I am writing a database to catalog the column names in a series of
workbooks... How do I iterate through the worksheets in a workbook
from Access using ADO.

okay. swallow what you're eating/drinking... don't want to be
responsible for food-related mishaps.

here's my code... how do I fix it so that it will loop through all
worksheets in the workbook using ADO(X) and not the Excel object
model? I know how to do it with Excel

for each worksheet in activeworkbook.worksheets (or something like it)
do something
next worksheet

but how do I do this with just ADO?

Thanks

Option Compare Database
Option Explicit

Public Sub CatalogFieldNames(ByVal strFileName As String)

Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim intCounter As Integer
Dim rs As ADODB.Recordset

'--open the ACCESS table to record the field names
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "XL_Columns", CurrentProject.Connection, , , adCmdTable


'--open the EXCEL file in ADO and read the field names
Set cnn = New ADODB.Connection
With cnn 'FileName is the name of the file you want to open
[String]
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source = " & strFileName & ";
Extended Properties = Excel 8.0;"
.Open
End With

Set cat = New ADOX.Catalog 'get the Catalog Information
connected up
cat.ActiveConnection = cnn

'--loop through the sheets... (I think... tweak later)
For Each tbl In cat.Tables
Debug.Print tbl.Name & " contains " & tbl.Columns.Count & "
columns"
'--iterate through the columns in the sheet...
For intCounter = 0 To tbl.Columns.Count - 1
Debug.Print tbl.Columns(intCounter).Name ',
tbl.Columns(intCounter).Type, tbl.Columns(intCounter).DefinedSize
rs.AddNew
rs.Fields("FileName") = strFileName
rs.Fields("ColumnName") = tbl.Columns(intCounter).Name
rs.Update
Next intCounter
Next

rs.Close
Set rs = Nothing
Set cat = Nothing
cnn.Close
Set cnn = Nothing

End Sub

Do I have to use the Excel object model?...

thanks,

Pieter

 

[John Nurick]

Hi Pieter,

If you search (e.g. at groups.google.com) for
collins getwsnames
you'll find a working procedure that Jamie Collins posted ages ago. It
uses ADO to get a list of worksheet names. Maybe it'll help you spot a
problem in your code; if not it should be easily modified by adding an
second loop to iterate through the columns in each table.

I have a really dumb question...

I am writing a database to catalog the column names in a series of
workbooks... How do I iterate through the worksheets in a workbook
from Access using ADO.

okay. swallow what you're eating/drinking... don't want to be
responsible for food-related mishaps.

here's my code... how do I fix it so that it will loop through all
worksheets in the workbook using ADO(X) and not the Excel object
model? I know how to do it with Excel

for each worksheet in activeworkbook.worksheets (or something like it)
do something
next worksheet

but how do I do this with just ADO?

Thanks

Option Compare Database
Option Explicit

Public Sub CatalogFieldNames(ByVal strFileName As String)

Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim intCounter As Integer
Dim rs As ADODB.Recordset

'--open the ACCESS table to record the field names
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "XL_Columns", CurrentProject.Connection, , , adCmdTable


'--open the EXCEL file in ADO and read the field names
Set cnn = New ADODB.Connection
With cnn 'FileName is the name of the file you want to open
[String]
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source = " & strFileName & ";
Extended Properties = Excel 8.0;"
.Open
End With

Set cat = New ADOX.Catalog 'get the Catalog Information
connected up
cat.ActiveConnection = cnn

'--loop through the sheets... (I think... tweak later)
For Each tbl In cat.Tables
Debug.Print tbl.Name & " contains " & tbl.Columns.Count & "
columns"
'--iterate through the columns in the sheet...
For intCounter = 0 To tbl.Columns.Count - 1
Debug.Print tbl.Columns(intCounter).Name ',
tbl.Columns(intCounter).Type, tbl.Columns(intCounter).DefinedSize
rs.AddNew
rs.Fields("FileName") = strFileName
rs.Fields("ColumnName") = tbl.Columns(intCounter).Name
rs.Update
Next intCounter
Next

rs.Close
Set rs = Nothing
Set cat = Nothing
cnn.Close
Set cnn = Nothing

End Sub

Do I have to use the Excel object model?...

thanks,

Pieter

 

[pietlinden]

Hi Pieter,

If you search (e.g. at groups.google.com) for
collins getwsnames
you'll find a working procedure that Jamie Collins posted ages ago. It
uses ADO to get a list of worksheet names. Maybe it'll help you spot a
problem in your code; if not it should be easily modified by adding an
second loop to iterate through the columns in each table.

I have a really dumb question...

I am writing a database to catalog the column names in a series of
workbooks... How do I iterate through the worksheets in a workbook
from Access using ADO.

okay. swallow what you're eating/drinking... don't want to be
responsible for food-related mishaps.

here's my code... how do I fix it so that it will loop through all
worksheets in the workbook using ADO(X) and not the Excel object
model? I know how to do it with Excel

for each worksheet in activeworkbook.worksheets (or something like it)
do something
next worksheet

but how do I do this with just ADO?

Option Compare Database
Option Explicit

Public Sub CatalogFieldNames(ByVal strFileName As String)

Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim intCounter As Integer
Dim rs As ADODB.Recordset

'--open the ACCESS table to record the field names
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "XL_Columns", CurrentProject.Connection, , , adCmdTable

'--open the EXCEL file in ADO and read the field names
Set cnn = New ADODB.Connection
With cnn 'FileName is the name of the file you want to open
[String]
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source = " & strFileName & ";
Extended Properties = Excel 8.0;"
.Open
End With

Set cat = New ADOX.Catalog 'get the Catalog Information
connected up
cat.ActiveConnection = cnn

'--loop through the sheets... (I think... tweak later)
For Each tbl In cat.Tables
Debug.Print tbl.Name & " contains " & tbl.Columns.Count & "
columns"
'--iterate through the columns in the sheet...
For intCounter = 0 To tbl.Columns.Count - 1
Debug.Print tbl.Columns(intCounter).Name ',
tbl.Columns(intCounter).Type, tbl.Columns(intCounter).DefinedSize
rs.AddNew
rs.Fields("FileName") = strFileName
rs.Fields("ColumnName") = tbl.Columns(intCounter).Name
rs.Update
Next intCounter
Next

rs.Close
Set rs = Nothing
Set cat = Nothing
cnn.Close
Set cnn = Nothing

End Sub

Do I have to use the Excel object model?...

Pieter

cool... thanks, John. I am not sure if I'll have a zillion tabs in
each sheet, but they're pretending they have something like a
relational database in Excel, so it wouldn't surprise me... I'll
search around and copy the example