Need Help with VBA Code to Automate Data Import into Access

[gregbowers]

Hello


I am working on a project in Microsoft Access and need some advice with VBA code to automate the data import process.
I am trying to achieve ; the data is in multiple Excel files located in a specific folder. I want to import the data into an existing Access table.

The Excel files have a consistent structure ; I need to loop through all the Excel files in the folder and import the data into the Access table.
I would like to include error handling to skip files that might have issues and log any errors.

I have referred https://stackoverflow.com/questions/14155163/linq-entities-everything-or-nothing-in-database/14155600#14155600#msbi guide but still need help .

I have some experience with VBA but am not sure how to put all these pieces together. Any guidance on how to accomplish this would be greatly appreciated.

Thank you in advance for your help!


Best regards,

gregbowers

[xps350]

We need more info, like:
What kind of data?
What is the problem?

Edit: I saw an empty post at first.

[Aussiebear]

Would any of these give you a starting point

To loop through files in a folder (uses early binding)

Sub LoopFilesInFolder()
    Dim folderName As String
    Dim FSOLibrary As FileSystemObject
    Dim FSOFolder As Object
    Dim FSOFile As Object
    'Set the file name to a variable
    folderName = "C:\Users\Aussiebear\Documents\"
    'Set all the references to the FSO Library
    Set FSOLibrary = New FileSystemObject
    Set FSOFolder = FSOLibrary.GetFolder(folderName)  
    'Use For Each loop to loop through each file in the folder
    For Each FSOFile In FSOFolder.Files
        'Insert actions to be perfomed on each file
        'This example will print the file name to the immediate window
        Debug.Print FSOFile.Name
    Next
    'Release the memory
    Set FSOLibrary = Nothing
    Set FSOFolder = Nothing
End Sub

To loop through files in a folder (uses late binding)

Sub LoopAllFilesInFolder()
    Dim folderName As String
    Dim FSOLibrary As Object
    Dim FSOFolder As Object
    Dim FSOFile As Object
    'Set the file name to a variable
    folderName = "C:\Users\Aussiebear\Documents\"
    'Set all the references to the FSO Library
    Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
    Set FSOFolder = FSOLibrary.GetFolder(folderName)
    'Use For Each loop to loop through each file in the folder
    For Each FSOFile In FSOFolder.Files
        'Insert actions to be perfomed on each file
        'This example will print the file name to the immediate window
        Debug.Print FSOFile.Name
    Next
    'Release the memory
    Set FSOLibrary = Nothing
    Set FSOFolder = Nothing
End Sub

To send data to access

Sub ADOFromExcelToAccess()
    ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=C:\FolderName\DataBaseName.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
        ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

[Gasman]

I would use Aussiebear's code for processing the folder for files and TransferSpreadsheet to the movement of data.