VBA to pull data from Access DB to Excel RRS feed

  • Question

  • I VBA written in an Excel Module

    Sub queryAccess()
    'Add the reference for MS Access and Microsoft DAO
    'GoTo Tools References search down the list for Microsoft Access XX.Object Library
    'Tick the box and do the same for Microsoft DAO XX.Object Library
    Dim db As DAO.Database
    Dim ws As Worksheet
    Dim rst As DAO.Recordset
    Dim lngCount As Long
    Dim i As Long
    Set db = OpenDatabase("C:\Access\Obligor Risk Rating\Obligor Risk Rating v1.2.mdb")
    Set rst = db.OpenRecordset("rpt_all")
    Set ws = Sheet1
    lngCount = 1
    For i = 0 To rst.Fields.Count - 1
    ws.Range("a2").Offset(0, i).Value = rst.Fields(i).Name
    Do Until rst.EOF
        With ws
             .Cells(lngCount + 1, 1).Value = rst![Legal Entity CID]
             .Cells(lngCount + 1, 2).Value = rst![Operating CID] 
             .Cells(lngCount + 1, 3).Value = rst![Approved Date]
             .Cells(lngCount + 1, 4).Value = rst![Approved Rating]
             .Cells(lngCount + 1, 5).Value = rst![Rating Required]
        End With
        lngCount = lngCount + 1
    Set rst = Nothing
    End Sub

    using DAO to pull in data from an Access database. I have two issues that I can not figure out:

    1) The results are shown in columns A-E as expected, but columns F-AK are showing the column headers for the rest of the columns in the table  that I do not want display. 

    2) I don't know how to get the four column headers I want ([Legal Entity CID], [Operating CID], [Approved Date], [Approved Rating] and [Rating Required]) to show up in the first row

    Thursday, April 28, 2016 6:41 PM


All replies