Consulting

Results 1 to 4 of 4

Thread: Need Help with VBA Code to Automate Data Import into Access

  1. #1
    VBAX Regular
    Joined
    Jun 2024
    Location
    US
    Posts
    8
    Location

    Need Help with VBA Code to Automate Data Import into Access

    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

  2. #2
    VBAX Regular xps350's Avatar
    Joined
    Jul 2022
    Posts
    12
    Location
    We need more info, like:
    What kind of data?
    What is the problem?

    Edit: I saw an empty post at first.
    Last edited by xps350; 06-02-2024 at 01:55 PM.
    Groeten,

    Peter

  3. #3
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,181
    Location
    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
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    VBAX Contributor
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    102
    Location
    I would use Aussiebear's code for processing the folder for files and TransferSpreadsheet to the movement of data.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •