By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Read Excel Spreadsheet Names from Access

P: n/a
Is it possible to read the spreadsheet names in an Excel workbook with VBA
and no Excel?
Mar 16 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Mar 17 '08 #2

P: n/a
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

Mar 17 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.