‘VBA Code to import all excel files from a certain directory and certain range to an Access table.

'The paths and names should be changed for your project.

‘Just put the code behind a command button on a form.

 

Public Sub Import()

Dim strFile As String

DoCmd.SetWarnings False

Dim db As Database

Dim strFileName As String

Dim dFileDate As Date

Set db = CurrentDb()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Dim rec As Recordset

'*********************************************

Msg = ""

Style = vbYesNo + vbWarning + vbDefaultButton2

Title = ""

Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then    ' User chose Yes.

    MyString = "Yes"    ' Perform some action.

'*********************************************

DoCmd.SetWarnings False

On Error GoTo ErrorHandler

    DoCmd.SetWarnings False

    strPath = "J:\your directory\"

'   Tell it to import all Excel files from the file directory

    strFile = Dir(strPath & "*.xlsx*")

'   Start loop

'*********************************************

    Do While strFile <> ""

        ' Import file

        DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="YourAccessTableName", FileName:=strPath & strFile, HasFieldNames:=False, Range:="A2:AN800"

        ' Loop to next file in directory

        strFile = Dir

    Loop

'*********************************************

DoCmd.SetWarnings True

 MsgBox "The file(s) have been imported."

  Exit Sub

ErrorHandler:

MsgBox "There was an Error: " & Err & ": " & Error(Err)

 

Else    ' User chose No.

    MyString = "No"    ' Perform some action.

End If

End Sub