importing MULTIPLE excel files simultaneously

  • Thread starter Thread starter William
  • Start date Start date
W

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
 
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
 
Thanks!
--
William


Scott Lichtenberg said:
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
Click to expand...
 
Back
Top