NEWBIE Needs To Import All Records From A Folder

[RayportingMonkey]

OK - First things first... This is literaly like the 5th time I have ever
even opened Access - so Access terms and methods are still foreign to me. I
do a lot of VBA in Excel and I am told that Access VBA is very similar, so I
am reaching out for help. Again, I would have searched this out first, but I
don't know the right terms to search on!

I am sure this is pretty simple...

Once I have opened the mdb file, here are the literal steps I am doing:

Click NEW

Import Table

Select the file (ultimately I need to loop through all the files in a given
directory. There are between 75 and 3,000 text files in each directory - Each
table only corresponds to one directory though).

From the Import Text Wizard I click Advanced, Specs, and the existing
Specification and click OK, which brings me back to the Import Text Wizard

Delimited > Next

Delimiter = |
and I have the "First Row Contains Field Names" box checked
Click Next

Specify the name of the existing table
Click Finish

Once the record is imported, I am manually moving the record to an
"Imported" folder beneath the directory where the text files reside.

Again, I apologize for the simplicity of my question - If I wasn't sure in
Excel, I would start with the MacroRecorder to get me started, but that
feature doesn't seem to exist in Access.

I know the solution will literally save me hours - I really appreciate the
assistance!!!

Regards,
Ray

 

[RayportingMonkey]

Well, no replys, but with the hopes of helping someone out in the future,
here's the code I came up with:


Option Compare Database
Option Explicit

Sub ImportBatch()

'This code will import/append all the specified
'files into the applicable table.

'Set Project Variables
Dim PrjPath As String
PrjPath = Application.CurrentProject.Path
Dim Fls As Integer

'Change the following variables depending
'on your requirements.
Dim SrchDir As String
SrchDir = "Enter_Full_Path_To_Search_Here"
Dim FileType As String
FileType = "Enter_Search_Criteria_like: *.txt"
Dim TblNam As String
TblNam = "Enter_Table_Name_Here"
Dim SpecNam As String
SpecNam = "Enter_Import_Specification_Here"

'This is the code that imports the applicable files
'into the table specified above.
With Application.FileSearch
.LookIn = SrchDir
.SearchSubFolders = False
.FileName = FileType

If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For Fls = 1 To .FoundFiles.Count
DoCmd.TransferText acImportDelim, SpecNam, TblNam,
..FoundFiles(Fls), True, ""
Next Fls
Else
MsgBox "There were no files found."
End If
End With

End Sub