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]

Re: Read Excel Spreadsheet Names from Access

Yes it is:

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

Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConne ction = 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]

Re: Read Excel Spreadsheet Names from Access

Thanks very much!

"The Frog" <Mr.Frog.to.you @googlemail.com wrote in message
news:8c3eb8a2-ee95-40b9-864f-1b2b72c6feb2@b6 4g2000hsa.googl egroups.com...

Yes it is:
>
Dim cnn As ADODB.Connectio n
Set cnn = New ADODB.Connectio n
With cnn
.Provider = "Microsoft.Jet. OLEDB.4.0"
.ConnectionStri ng = "Data Source = " & FileName & "; Extended
Properties = Excel 8.0;"
.Open
End With
>
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConne ction = 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