Import Multiple Excel Spreadsheets into Access 1

[futaba]

I am receiving multiple spreadsheets in varying quantities and with varying names via e-mail (normally between 10 and 20 Spreadsheets per e-mail). The data within the spreadsheets is always consistant.I am currently importing each spreadsheet individually into one table within Access, is there a way of automating this process ie. importing all files within a specific folder regardless of the files names, or qty of individual files within the folder, when the import is triggered.

The spreadsheets are being sent from Japan and are created by core buisness systems on different sites so I have no option for changing the source of the data.

 

[TopJack]

I haven't tested this but the theory should work .....

Sub Import_Files()
Dim ImportFolder As String, FileSearch As String, FileName As String

ImportFolder = "C:\e-mail_folder\" 'folder containing files to import

FileSearch = ImportFolder & "*.xls" 'set file name search

FileName = Dir(FileSearch) 'first file entry

Do While FileName <> &quot;&quot; 'search the whole folder
If (FileName <> &quot;.&quot; And FileName <> &quot;..&quot Then 'ignore directories
DoCmd.TransferSpreadsheet acImport, 8, &quot;New Table - &quot; & FileName, ImportFolder & FileName, _
True, &quot;A1:G100&quot; 'import excel file
End If
FileName = Dir 'get next file entry
Loop

End Sub

Hope this helps.