[RESOLVED]Using Ado To Access Excel Files

[RobDog888]

Your still not opening a ADODB connection and placing the reverse single quote around the sheet name.

Private Sub Form_Load()
    
    Dim sPath As String
    Dim oRs as ADODB.Recordset
 
    sPath = App.Path & "\TestFile.xls"
    
    Set oRs = GetADOExcelRecordSet(sPath)
    
End Sub
 
 
Private Function GetADOExcelRecordSet(ByVal Path As String, _
                Optional ByVal Headers As Boolean = True) As Recordset
               
    Dim sCnn As String
    Dim sWorksheet As String
    Dim sRequest As String
    
    sWorksheet = "Sheet1"
    
    Dim oCnn As ADODB.Connection
    Set oCnn = New ADODB.Connection
    Dim oRs As Recordset
    Set oRs = New ADODB.Recordset
    
    sCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Path & ";" & _
            "Extended Properties=""Excel 8.0;HDR=No" & _
            IIf(Headers, "Yes", "No") & """"
            
    oCnn.ConnectionString = sCnn
    oCnn.Open
 
    'Read a sheet:
 
    sRequest = "SELECT * FROM [`" & sWorksheet & "$`]"
    oRs.Open sRequest, oCnn, adOpenDynamic, adLockOptimistic
    Set GetADOExcelConnection = oRs
    
End Function