getting sheets when using ADOX Catalog to read Excel

  • Thread starter Thread starter pietlinden
  • Start date Start date
P

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
 
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
Click to expand...
 
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...
Click to expand...
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.
Click to expand...
okay. swallow what you're eating/drinking... don't want to be
responsible for food-related mishaps.
Click to expand...
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
Click to expand...
for each worksheet in activeworkbook.worksheets (or something like it)
do something
next worksheet
Click to expand...
but how do I do this with just ADO?

Option Compare Database
Option Explicit
Click to expand...
Public Sub CatalogFieldNames(ByVal strFileName As String)
Click to expand...
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
Click to expand...
'--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
Click to expand...
'--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
Click to expand...
Set cat = New ADOX.Catalog 'get the Catalog Information
connected up
cat.ActiveConnection = cnn
Click to expand...
'--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
Click to expand...
rs.Close
Set rs = Nothing
Set cat = Nothing
cnn.Close
Set cnn = Nothing
Click to expand...
End Sub
Click to expand...
Do I have to use the Excel object model?...

Pieter
Click to expand...
Click to expand...

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
 
Back
Top