Need to import multiple fixed width files into to a master table

[Guest]

I have several hundred fixed width text files that i need to append to a
master table in access. I have created a macro that will append one file at a
time with the saved Spec to the master table, but this involves me going into
the macro's properties and changing the pathfile for each individual file.
This can be very monotnus after awhile. Is there way I can import several
fixed width text files all at once and can I do it through Access 2000?
Thanks inadvance.

 

[John Nurick]

I don't think you can do this in a macro, but it's quite simple using
VBA. The following code has been posted several times here by my fellow
MVP Joe Fallon:

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