Open Excel using ADO

[petermeachem]

i am trying to display the contents of a spreadsheet in a grid in vb.

What I am doing is:

cConnectionString=
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= D:\Letters\Letters.xls;Extended Properties=Excel 5.0;"

Set cnn = New ADODB.Connection
cnn.ConnectionString = cConnectionString
cnn.Open

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open csql, cnn, adOpenStatic, adLockOptimistic

where csql is
SELECT * From [Sheet1$] IN 'D:\Letters\Letters.xls'

Which gives me 'Unrecognized database format'

Would someone like to tell me where my weakest link is?

Peter Meachem
peter@accuflight.com

 

[Bridgit]

You are using an MSAccess 2000 driver to connecct to an Excel spreadsheet. Not working...
Build a DSN and refer to it in your connection:
cnn.open "dsn=YourNewDSN"

 

[petermeachem]

Bridgit,

Are you sure about that. This is straight a MS doc about changing from dao to ado.

Sub ADOOpenISAMDatabase()

Dim cnn As New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Sales.xls;Extended Properties=Excel 8.0;"

cnn.Close

End Sub


Peter Meachem
peter@accuflight.com

 

[petermeachem]

Bridgit,

Are you sure about that. This is straight from an MS doc about changing from dao to ado.

Sub ADOOpenISAMDatabase()

Dim cnn As New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Sales.xls;Extended Properties=Excel 8.0;"

cnn.Close

End Sub


Peter Meachem
peter@accuflight.com