Help! Import Multiple Excel files into Access Automatically

[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

 

[hockey is fun]

you could use a union query to extract the data from multipule sources

 

[Klatuu]

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