VBA : Read From XLS File and Return Recordset based on a given Select Statement

Usage Sample:

    Dim oRs As New ADODB.Recordset
    If cmbGov.Text = “” Then
        Set oRs = getXLSSet(“Select * from [Area$]”)
        Set oRs = getXLSSet(“Select * from [Area$] where Gov = ‘” & cmbGov.Text & “‘”)
    End If
    Dim y As Integer
    For y = 1 To oRs.RecordCount
        frmhome.cmbArea.AddItem (oRs.Fields(0).Value)

‘Function to read XLS Files based on a given select statement
Public Function getXLSSet(sourceStr As String) As Recordset
On Error GoTo Catch
    Dim oRs As ADODB.Recordset, oConn As ADODB.Connection, sConString As String, vValue As Variant
    sConString = “Provider= Microsoft.Jet.OLEDB.4.0;” & ” Data Source=” & sXLFile & “;Extended Properties=Excel 8.0;”
    Set oConn = New ADODB.Connection
    With oConn
        .CursorLocation = adUseClient
        .Open sConString
    End With
    Set oRs = New ADODB.Recordset
    With oRs
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockPessimistic
        .Source = sourceStr
        .ActiveConnection = oConn
    End With
    ”Work with the RS
        Set getXLSSet = oRs
    Exit Function
    ‘MsgBox “Can’t Process Your Request” & vbCr _
    ‘& “Please Check you Levels or Excel File”
End Function


Posted on January 4, 2010, in Excel, Source Code, VBA. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: