Automate Multiple import .txt

  • Thread starter Thread starter john
  • Start date Start date
J

john

I am setting up a database for which i need to import data
from text files. I have setup a macro and specification to
import the file c:\import\import.txt

However there are hundreds of these .txt files I need to
import and need help on automating it. I would like to
drop all files in the c:\import directory and have Access
iterate through each until all are imported. In concept I
have thought of the following.

1) Write a For Loop that Sets an array of the files in
that folder and imports each one , or with *.txt

2) Find the first file in the folder, rename it to
import.txt, import the data, delete import.txt. This loops
until finished.

However I have not done much filesystem object VBA and a
little lost on how to tackle this one.

Regards,

John
 
How to Import all Files in a Folder:

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
 
Maybe try something like:

Function import()
path = "c:\import\"
myfile = Dir(path + "*.txt", vbHidden) 'gets first txt
file in path specified

Do While myfile <> "" 'will cause to loop through all
txt files in path
DoCmd.TransferText acImportDelim, "YOUR IMPORT
SPECIFICATION NAME", "TABLE NAME", path + myfile,
0 'imports file
myfile = Dir 'grabs next txt file
Loop
End Function

This would import ALL .txt files in the directory using
your specification. Then delete the files.
 
Back
Top