Read Excel Spreadsheet Names from Access

[KC-Mass]

Is it possible to read the spreadsheet names in an Excel workbook with VBA
and no Excel?

[The Frog]

Yes it is:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source = " & FileName & "; Extended
Properties = Excel 8.0;"
.Open
End With

Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = cnn

Dim tbl As ADOX.Table
For Each tbl In cat.tables
'do something
loop
set tbl = nothing
set cat = nothing
cnn.close
set cnn=nothing

You can get the column names as well with the ADOX.Column object.
eg/
dim fld as ADOX.Column
for each fld in tbl.columns
'do something
loop

You need to make a reference to the ADO and ADOX object libraries in
tools-references.

Hope this helps

Cheers

The Frog

[KC-Mass]

Thanks very much!

"The Frog" <Mr************@googlemail.comwrote in message
news:8c**********************************@b64g2000 hsa.googlegroups.com...

Yes it is:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source = " & FileName & "; Extended
Properties = Excel 8.0;"
.Open
End With

Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = cnn

Dim tbl As ADOX.Table
For Each tbl In cat.tables
'do something
loop
set tbl = nothing
set cat = nothing
cnn.close
set cnn=nothing

You can get the column names as well with the ADOX.Column object.
eg/
dim fld as ADOX.Column
for each fld in tbl.columns
'do something
loop

You need to make a reference to the ADO and ADOX object libraries in
tools-references.

Hope this helps

Cheers

The Frog