VBA: Import Multiple Excel Files into Access
Say you have multiple excel files (for example daily feeds) in the same format and want to import the data into MS Access. Instead of opening each file and copy pasting the data into Access, the easier way is to use the VBA script below.
Just make sure you create a folder where you keep your excel files and specify it in the code blow by replacing the following line 9.
Also, make sure if you are going to use the folder in the future that you remove the old files.
1. Private Sub Command3_Click()
2. Dim strFile As String ‘Filename
3. Dim strFileList() As String ‘File Array
4. Dim intFile As Integer ‘File Number
5. Dim filename As String
6. Dim path As String
7.
8. DoCmd.SetWarnings False
9. path = “C:\Test\”
10.
11. ‘Loop through the folder & build file list
12. strFile = Dir(path & “*.xls”)
13.
14. While strFile <> “”
15. ‘add files to the list
16. intFile = intFile + 1
17. ReDim Preserve strFileList(1 To intFile)
18. strFileList(intFile) = strFile
19. strFile = Dir()
20. Wend
21.
22. ‘see if any files were found
23. If intFile = 0 Then
24. MsgBox “No files found”
25. End If
26.
27. ‘cycle through the list of files
28. For intFile = 1 To UBound(strFileList)
29. filename = path & strFileList(intFile)
30. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, “tblClientMail”, filename, True
31. Next intFile
32.
33. DoCmd.SetWarnings True
34.
35. End Sub
