Import multiple Excel files into Access

[theodorej]

......how do I select multiple Excel files to import into Access?
Any insights would be deeply appreciated.

[MMcCarthy]

......how do I select multiple Excel files to import into Access?
Any insights would be deeply appreciated.

Are all these files being imported into separate tables or the one table?

[theodorej]

Are all these files being imported into separate tables or the one table?

Moving them all into one table would be preferable; each source file is approx. 300 rows and contains data on a monthly basis from January, '95 to Current. I hope I'm telling you enough to answer the question.

[MMcCarthy]

Try something like this ...

Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim path As String
Dim filename As String
Dim strSQL As String
 
    path = "path to your folder containing the files"
    Set db = CurrentDb
    DoCmd.SetWarnings False
 
    'Loop through the folder & build file list
    strFile = Dir(path & "*.xls")
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
     'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
 
    'cycle through the list of files
    For intFile = 1 To UBound(strFileList)
       filename = path & strFileList(intFile)
       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "TableName", filename, True 'replace TableName with your table name
 
    Next intFile

Mary