CODEWITHSUNDEEP
vbams-access
Golden Sun
Golden Sun

Reputation: 63

Read data from Access file into Excel

I am trying to read/import data from a table in Access into a tab in an Excel spreadsheet. The data will be names/positions/compensations of certain companies. The headers of the columns in both tables (Access and Excel) are the same.

When I read the data, the logic is - locate the company ID in another Access table and then retrieve the data from the Access table that stores the data (the company ID is important since the table stores the data of every company and ID is used to identify which company's data to extract).

This is the template I got from online:

Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, _
    FieldName As String, TargetRange As Range)
' Example: DAOCopyFromRecordSet "C:\FolderName\DataBaseName.mdb", _
    "TableName", "FieldName", Range("C1")
Dim db As Database, rs As Recordset
Dim intColIndex As Integer
    Set TargetRange = Sheets("MIC").Cells(1, 1)
    Set db = OpenDatabase(DBFullName)
    Set rs = db.OpenRecordset(AIF_MIC, dbOpenTable) ' all records
    'Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _
        " WHERE " & FieldName & _
        " = 'MyCriteria'", dbReadOnly) ' filter records
    ' write field names
    For intColIndex = 0 To rs.Fields.Count - 1
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    Next
    ' write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

My actual code:

Sub DAOCopyFromRecordSet("H:\HAPPY\Happy Folder\Happy DB.mdb", "Happy_Table","A,B,C" ,Sheets("Happy").Range("C5:M32")
Dim db As Database, rs As Recordset
Dim intColIndex As Integer
    Set TargetRange = Sheets("Happy").Cells(1, 1)
    Set db = OpenDatabase(DBFullName,false,false,";pwd=HAPPY")
    Set rs = db.OpenRecordset(Happy, dbOpenTable) ' all records
    'Set rs = db.OpenRecordset("SELECT * FROM " & Happy & _
        " WHERE " & FieldName & _
        " = 'MyCriteria'", dbReadOnly) ' filter records
    ' write field names
    For intColIndex = 0 To rs.Fields.Count - 1
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    Next
    ' write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

My target cells are C5:M32 in my spreadsheet - sheets "happy".
How do I import the data into the cells belonging to each column with different header names?
Also I have to make sure the coding is giving me the data for the correct company since all companies have the same type information saved in the Access table.

Upvotes: 2

Views: 271

Answers (1)

June7
June7

Reputation: 21379

This procedure is designed to run in a General Module in Excel VBA, make sure that's what you really want.

For starter, the Sub declaration is wrong. This is not where you specify the database, table, field, range. Look at the example you adapted. It has variable arguments in the declaration, not literal strings. Could remove the arguments and hard code source within procedure. Since you specify TargetRange within procedure, can certainly eliminate the TargetRange argument.

Since code is declaring specific type objects (Database and Recordset), need to set reference in VBE > Tools > References > MS Office x.x Access database engine Object Library. This is 'early binding'.

One issue will probably encounter is if this is overwriting data already in cells, really need to clear the data or could end up with leftover data if new data does not extend through end of existing rows.

No idea how you want to obtain filter criteria for the SQL statement. Could prompt user for inputs.

I tested and following worked for me.

Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, FieldName As String)
Dim db As Database, rs As Recordset
Dim intColIndex As Integer
Dim TargetRange As Range
    Set TargetRange = Sheets("Happy").Range("C5")
    Set db = OpenDatabase(DBFullName,false,false,";pwd=HAPPY")
    Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records
    'Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _
        " WHERE [" & FieldName & "] = " & InputBox("Enter customer ID", , 0), dbReadOnly) ' filter records
    'clear old data
    Sheets("Happy").Range("C:M").Value = ""
    ' write field names
    For intColIndex = 0 To rs.Fields.Count - 1
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    Next
    ' write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

Call the procedure in an event such as a button click.

DAOCopyFROMRecordSet "H:\HAPPY\Happy Folder\Happy DB.mdb", "Happy_Table", "CustomerID"

Upvotes: 1

Related Questions