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# (http://https://stackoverflow.com/questions/14155163/linq-entities-everything-or-nothing-in-database/14155600#14155600#)msbi (https://www.igmguru.com/data-science-bi/msbi-certification-training/) 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.