Help! Import Multiple Excel files into Access Automatically

  • Thread starter Thread starter Ingrid L.
  • Start date Start date
I

Ingrid L.

Hi! I'm a novice and new to this board.
Need some help pls.
I have an access database built by a contractor who used VB language to run
a macro or script that would automatically update the database from multiple
excel files that were dragged and dropped to a folder.
We can't find the contractor now to make changes to the database so we built
a new one.
our internal expert has Access expertise and can create a macro that will
update the database so long as it's from one source file.
Clearly it can be done we just can't figure out how to do it.
Can anyone pls point me in the right direction?
Thanks in advance.
Ingrid
 
You can't really do that with macros. You will have to use VBA to loop
through all the xls files in the folder and import them. If you are not
familiar with VBA, it will be a challenge; however, here is an example of how
it can be done:

Dim strFileName As String
Dim strPath as String

strPath = "c:\ExecelImports\"
strFileName = Dir(strPath & "*.xls")

Do While strFileName <> vbNullString
Docmd.TransferSpreadsheet acImport, , "TableNameGoesHere", strPath &
strFileName, True
strFileName = Dir()
Loop
 
Back
Top