multiple Excel files importing to Access Question

  • Thread starter Thread starter Douglas J. Steele
  • Start date Start date
D

Douglas J. Steele

You can write VBA code to loop through the folder (using the DIR statement),
getting each .XLS file and then use the TransferSpreadsheet method to import
each of the XLS files returned.
 
My company is looking to import multiple Excel (same
format) files into a pre-existing Access DB (already
designed to receive this Access format).

Only thing is, they have to process files one at a time,
using the way they do it now. Is there a good way to set
this up to "push a button" and get all the files to
append/update at once?
 
How to Import all Files in a Folder: (modify to meet your specific needs.)

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName",
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub
 
Back
Top