VB Forum / Database Access / December 2006

¤ Hi Guys,
¤
¤ I'm new at ADO and really wanting some sample code of how to use it to open
¤ a known xls, and get the list of worksheet names. Then once a worksheet name
¤ is known, how to get the info from it. Also, can I get the cell formatting
¤ info as well? or just the contents?

ADO can provide database related information but nothing concerning cell formatting. Below is an
example:

Dim cnn As New ADODB.Connection
Dim rsSchema As New ADODB.Recordset
Dim fld As ADODB.Field

On Error Resume Next

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=C:\Test Files\Book20.xls;Extended Properties=Excel 8.0;"

'Open the tables schema rowset
Set rsSchema = cnn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, Empty))
'Open the columns schema rowset
'Set rsSchema = cnn.OpenSchema(adSchemaColumns, Array(Empty, Empty, "Sheet1", Empty))

'Loop through the results and print the names in the Immediate window
While Not rsSchema.EOF
   
   For Each fld In rsSchema.Fields
       Debug.Print fld.Name & " : " & fld.Value
   Next fld
   
   rsSchema.MoveNext
Wend
 
rsSchema.Close
cnn.Close

Paul
~~~~
Microsoft MVP (Visual Basic)