[RESOLVED]Using Ado To Access Excel Files

[RobDog888]

Done! The function name was not matching to its signature.

GetADOExcelRecordSet <> GetADOExcelConnection

It didnt know where to return it to since it didnt match.

Also, no need for the reverse single quotes in this situation.

I added my code I already posted to establish the connection too.

Option Explicit
 
Private Sub Form_Load()
    
    Dim sPath As String
    Dim oRs As ADODB.Recordset
 
    sPath = App.Path & "\TestFile.xls"
    
    Set oRs = GetADOExcelRecordSet(sPath)
    oRs.MoveFirst
    oRs.Move 5
    MsgBox oRs.Fields(1).Value '6th row, column B
    
End Sub
 
 
Private Function [b]GetADOExcelRecordSet[/b](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 [b]GetADOExcelRecordSet[/b] = oRs
    
End Function