Snasui Excel Forum

Code: Select all

Sub GetData()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String
Dim B As String
B = Format(Year(Date), "0000") & Format(Month(Date)-1, "00")
    strFilePath = "D:\Monthly\Cardlink\" & B & "\Rpt_Monthly\ROBINSON.MDB"
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Sheet4.Range("DataRange").ClearContents
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strFilePath & ";"
    'sQRY = "SELECT tblData.* FROM tblData"
    sQRY = "NEWCUST"
    rs.CursorLocation = adUseClient
    rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
    Application.ScreenUpdating = False
    Sheet4.Range("A2").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
    Exit Sub
End Sub