importing MULTIPLE excel files simultaneously

[William]

Is there a way to import multiple excel files into an access database
SIMULTANEOUSLY, or do the excel files have to be imported one by one? All
files have identical column names.

Thanks

 

[Scott Lichtenberg]

William -

What do you mean by "simultaneously"? If you are looking for a way to have
Access open multiple threads and pull in several files at the same time,
that's out of my league. If you are looking for a way to pull in multiple
files sequentially, as part of one job or function, you can do this
relatively easily with the DIR function. The code below reads through a
directory looking for .xls files. You can specify your own file pattern to
look for. For each file found, execute the TransferSpreadsheet action to
import.

Dim strFileName As String
strFileName = "a" 'Need to set file name to a non-blank value so
we go into the loop.

Do While strFileName <> ""
strFileName = Dir("c:\MyDirectory\*.xls")
If strFileName <> "" Then 'A file was found
'MsgBox strFileName
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7,
"MyTable", strFileName, True
End If
Loop

MsgBox "Done"

Hope this helps,
Scott

 

[William]

Thanks!
--
William

William -

What do you mean by "simultaneously"? If you are looking for a way to have
Access open multiple threads and pull in several files at the same time,
that's out of my league. If you are looking for a way to pull in multiple
files sequentially, as part of one job or function, you can do this
relatively easily with the DIR function. The code below reads through a
directory looking for .xls files. You can specify your own file pattern to
look for. For each file found, execute the TransferSpreadsheet action to
import.

Dim strFileName As String
strFileName = "a" 'Need to set file name to a non-blank value so
we go into the loop.

Do While strFileName <> ""
strFileName = Dir("c:\MyDirectory\*.xls")
If strFileName <> "" Then 'A file was found
'MsgBox strFileName
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7,
"MyTable", strFileName, True
End If
Loop

MsgBox "Done"

Hope this helps,
Scott