getting excel recorset from Access

[AT76]

Public Function GetExcelTables() As ADODB.Recordset

Dim cn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim connString As String


connString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=U:\ATellez\TEST AREA\Planner Db retrofit\MaterialsTables.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""


cn.ConnectionString = connString
cn.Open

Set Rs = New ADODB.Recordset

Rs.Open "MaterialsTables", cn, adOpenStatic, adLockOptimistic, adCmdTable

Rs.Open "select * from MaterialsTables", cn, , , adCmdTable

If Rs.EOF And Rs.BOF Then
....
....
....

End Function

 

[AT76]

I apologize for submitting the last post without being completed. I'm trying to open an excel spreadsheet from my access app. I need to access an excel recordset. Unfortunately I get the following error message:

Run-time error -2147217865
The Microsoft Jet databaseengine could not find the object 'MaterialsTables'. Make sure the object exists and that you spell its name and path correctly.

Well, the name and path are correct. The error occurs at line:

Rs.Open "select * from MaterialsTables", cn, , , adCmdTable

Any ideas how to fix this error? Thank you!

 

[PHV]

Doesn't the previous Rs.Open suffice ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AT76]

Not sure if I understand.

I tried just using Rs.Open but I got the following error:

Run-time error: 3709
The connection cannot be used to perform this operation. It is either closed or invalid in this context.

Thank you PHV!

 

[Remou]

Here is a thought:

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
'C:\MyFolder\MyWorkbook.xls;" &
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString =  & _
    "Data Source=U:\ATellez\TEST AREA\Planner Db retrofit\MaterialsTables.xls;" & _
"Extended Properties=Excel 8.0;"
    .Open
End With

'[b]Use name of worksheet[/b]
strquery = "SELECT * FROM [Sheet1$]" 
Set rs = CreateObject("ADODB.Recordset")
rs.Open strquery, cn, 3, 3
Debug.Print rs.Fields(1)

 

[AT76]

Remou, your example worked! Thank you much.

I'm still confused about mine. I will keep looking at it!

Appreciate all the help!