VBA Excel recordset

lukin

I have this problem that is driving me insane.
I have the VBA code below in an Access application; it is selecting from a Book in an Excel Sheet ("pstrReference" is the path to the Excel file and it is definitley correct).
It doesn't fail on the SQL inside the loop, it fails on the line "rsExcel.Open cmd2". The error message is:
"The Microsoft Jet database engine could not find the object 'Reagents'. Make sure the object exists and that you spell its name and the path name correctly."
There is a book called 'Reagents' in that Excel file and as I've said the path is correct, it's not read-only or anything.

Public Function Import_PLR_PL_COP_JD(pstrReference As String, i_NewRecs As Integer) As Boolean
       Dim rsExcel As New ADODB.Recordset
    Dim cnn2 As New ADODB.Connection
    Dim cmd2 As New ADODB.Command
    Dim dbs As Database
    Dim strSql As String
    On Error GoTo Err_Import_PLR_PL_COP_JD
    With cnn2
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & pstrReference & ";Extended Properties=""Excel 8.0;HDR=Yes;Readonly=true"""
        .Open
    End With
   
    Set dbs = CurrentDb()
    Set cmd2.ActiveConnection = cnn2
    cmd2.CommandType = adCmdText
  cmd2.CommandText = "SELECT [Parameter List ID],[Variant],[Reagent ID],[AccessedBy],[DateAccessed] FROM [Reagents] "


    ' Import Parameter Lists
     rsExcel.CursorType = adOpenStatic
    rsExcel.LockType = adLockOptimistic

    rsExcel.Open cmd2
    While Not rsExcel.EOF

        ' Check if the Reference Type exists. If not add it.
        If DCount("[Parameter List Name]", "tParameterList", "([Parameter List Name] = '" & rsExcel.Fields("Parameter List ID") & "') AND ([Variant] = '" & rsExcel.Fields("Variant") & "')") = 0 Then
            ' Add the row to the Reference Type Table
            rsExcel.Fields("AccessedBy") = OSUser
            rsExcel.Fields("DateAccessed") = Date
            strSql = "INSERT INTO tParameterList ([Parameter List Name],[SiteParameterListName],[Variant],[GXP Data?],[Reagent],[Migrating Site],[Site],[CreatedBy],[CreatedOn],[Path]) "
            strSql = strSql & "VALUES(""" & rsExcel.Fields("Parameter List ID") & ""","
            strSql = strSql & """" & rsExcel.Fields("Parameter List Name") & ""","
            strSql = strSql & """" & rsExcel.Fields("Variant") & ""","
            strSql = strSql & """Y"","
            strSql = strSql & """" & rsExcel.Fields("Reagent ID") & ""","
            strSql = strSql & """LIB"","
            strSql = strSql & """LIB"","
            strSql = strSql & """" & OSUser & ""","
            strSql = strSql & """" & Date & ""","
            strSql = strSql & """" & pstrReference & """);"
            CurrentDb.Execute (strSql)
            i_NewRecs = i_NewRecs + 1
        End If
        rsExcel.MoveNext
    Wend
    rsExcel.Close
    
    
    Import_PLR_PL_COP_JD = True


Exit_Import_PLR_PL_COP_JD:
    Set dbs = Nothing
    Set rsExcel = Nothing
Exit Function

Err_Import_PLR_PL_COP_JD:
    MsgBox Err.Description
    Import_PLR_PL_COP_JD = False
    GoTo Exit_Import_PLR_PL_COP_JD

End Function

lukin

Heh, the old "post it on boards and the problem solves itself straight after".
Works every time. I changed the Excel filename to one without spaces and it is OK now. Weird because I have the exact same code elsewhere with Excel filenames with spaces and there is no problem.