Import Multiple Text files in Folder

[Kirk P.]

I'm trying to automate the import of several tab-delimited text files all
stored in a common folder, and append the data to tblCPG. I've got this code
that compiles, but when I run it, it skips right over the code to the MsgBox
- nothing imports. I'm using Access '07.

Sub MassImport()

Dim strPath As String
Dim strFileName As String
Dim dbf As Database

Set dbf = CurrentDb
' Set the path to the directory where the files will be.
strPath = "C:\Documents and Settings\kphillips\My
Documents\Kirk\Accounting\Navision\Gross Revenue"
strFileName = Dir(strPath & ".txt") ' Retrieve the first entry.
Do While strFileName <> "" ' Start the loop.
DoCmd.TransferText acImportDelim, "CPG Import Spec", "tblCPG", strPath &
strFileName, True
strFileName = Dir ' Get next entry.
Loop

MsgBox "Objects imported successfully!", vbInformation, "Import Status"

End Sub

Help?

 

[Dion]

If the sub goes right to the msgbox, I suspect Do While strFileName <> ""
is returning "" for the first loop.
Try putting MsxBox strFileName right after the Do line, and see what it
returns - if it's blank, that's your problem

 

[Kirk P.]

My bad - it's a simple syntax error. I'm missing the last "\" after Gross
Revenue in strPath = "C:\Documents and Settings\kphillips\My
Documents\Kirk\Accounting\Navision\Gross Revenue"