Importing Text Files

[Claude]

I have a problem where I need to import a host of different text files from
a directory where the file names are not consistently the same. I know how
to do it no problem when I know the name but how do you do it when you
don't know the name? Thanks in advance.

Claude.

 

[Guest]

Here is a simple function that will loop through a specified directory
looking for the specified file type. You may need to make adjustments
depending on the type of text file you have (delimited or fixed) but this
should get you started:

Function fImportAllFiles()

Dim strfile As String
Dim strPath As String

'File path to the Text files
'
strPath = "PATH TO YOUR FILES"

'Change the default directory to the file path
'
ChDir strPath

'Find the first Text file to use to create a string
'
strfile = Dir("*.txt")

'Loop through the string & import
'
Do While Len(strfile) > 0

DoCmd.TransferText acImportFixed, , "TABLE NAME"

'delete the file (consider moving it to an Archive folder instead.)
'Kill strPath & "/" & strfile

'Call Dir to get the next file
'
strfile = Dir
Loop

End Function

 

[Claude]

Thank you very much. It looks perfect! I'll let you know how it goes.

Claude.

 

[Claude]

Any idea why it always defaults to my My Documents directory? Otherwise its
working perfect. Thanks again.

Claude

 

[Claude]

hehe never mind - stupid me. Figured it out. Thanks!