Importing text files

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

Can anyone help
I have a series of text files that I need to import on a
daily basis. At the moment I am doing it manually but
what I want to do is put all the text files into one
directory and then run VB code that will import each file
(same file format) and when it has finished importing that
file move it into an subdirectory called 'imported'
 
Simon:

If you are doing this manually, then during the import routine, (File -> Get
External Data) at some point in time you will using the Import Wizard. If
you click the Advanced button on the first dialog, then you can set up your
import specification very exactly and using the "Save As" button save the
specification for the particular file layout.

Then in VBA routine you'll write, simply use the TransferText method to make
the data transfer, specifying the saved Import Specification you created
using the Wizard.

To loop through a directory to get all the file names is pretty easy. You
use the dir function specifying Dir("c:\somedir\*.*) to get the first file,
and then simply call Dir() again (with no parameters) to get subsequent
files until Dir() returns an empty string. In a loop this would look like:

Dim strFileString as Variant
strFile = Dir("c:\somedir\*.*)
If len(strFile)>0 Then _
Docmd.TransferText ......
Do until len(strFile)
strFile = Dir()
If len(strFile)>0 Then _
docmd.TransferText
Loop
 
Back
Top