Need help using ADO to return a range of values

[bfreescott]

Below is a function into which I pass a filename with it's full path and a sheet name. I have used this function successfully with a named range in the past, but not with a sheet name and explicit range. It is not opening the recordset. When I use the immediate window to determine rs.Fields(0).Name, I get the value of C13, but that is the extent of what seems to be working.

Function Get_VPK_Allot(var1 As String, var2 As String) As Variant
'
    On Error GoTo ErrorHandler

    Dim Conn As ADODB.Connection
    Dim ConnString As String
    Dim rs As New ADODB.Recordset
    Set Conn = New ADODB.Connection

    ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & var1 & ";Extended Properties=Excel 12.0 Xml"
    Conn.Open ConnString

    If Not (Conn.State And 1) = 1 Then
        MsgBox "Source data cannot be found. Please check the location of the file you are querying."
        Set Conn = Nothing
        Set rs = Nothing
        Application.ScreenUpdating = True
        Application.StatusBar = False
        Exit Function
    End If

    rs.Open "SELECT * FROM [" & var2 & "$C13:K13];", Conn, adOpenStatic, adLockReadOnly

    If Not rs.BOF = True And rs.EOF = True Then
        Get_VPK_Allot = rs.Fields(0).Value
    End If

    rs.Close
    Conn.Close
    Set Conn = Nothing
    Set rs = Nothing

Exit Function
ErrorHandler:
    MsgBox "Error: " & Err.Number & " , " & Err.Description & Chr(13) & _
    "Procedure is: Get_VPK_Allot" & Chr(13) & ""
    rs.Close
    Conn.Close
    Set Conn = Nothing
    Set rs = Nothing
    Application.ScreenUpdating = True
    Application.StatusBar = False
'
End Function

 

[V_Malkoti]

The query you've passed is trying to get data only from one row - columns C to K of line 13.

rs.Open "SELECT * FROM [" & var2 & "$[COLOR=#0000ff]C13:K13[/COLOR]];", Conn, adOpenStatic, adLockReadOnly

So you're getting a recordset with no records - Top row of range is assumed to be headers.

If you want to query only a specific portion of the spreadsheet then specify the complete range (e.g. C13:K1000).

rs.Open "SELECT * FROM [" & var2 & "$[COLOR=#0000ff]C13:K1000[/COLOR]];", Conn, adOpenStatic, adLockReadOnly

Otherwise if you want to query the entire sheet as is, then you can just write "SheetName$"

rs.Open "SELECT * FROM [" & var2 & "$];", Conn, adOpenStatic, adLockReadOnly

 

[bfreescott]

Thanks. I made the same mistake when using named ranges before. With ADO, when you want a specific range, you must use the row immediately preceding the row you want to return values from as your first row in the range you query. I changed C13 to C12 and now it works.