automatically link all files in a specified folder

[G]

I have a folder with about 500 files that need to be linked in my database.
To do this manually for each one is too time consuming. I have created an
import spec and used some code from other posts in this group.

It will link the first text file, but on the next loop I get "invalid
procedure call or argument" on the line "sfname = Dir".

Here's my code:
Dim sdir As String
Dim sfname As String
Dim spath As String
Dim sspec As String

spath = "C:\Documents and Settings\Owner\My Documents\CJ\15181\"

sfname = Dir("*.txt")

Do While Len(sfname) > 0

DoCmd.TransferText acLinkDelim, "Link Spec", Left(sfname,
Len(Dir(sfname)) - 4), Dir & sfname, True

MsgBox ("file is linked")
sfname = Dir
Loop

I appreciate any assistance you can give.

 

[Douglas J. Steele]

It's because you've got a call to the Dir function inside the loop:

DoCmd.TransferText acLinkDelim, "Link Spec", Left(sfname, Len(Dir(sfname)) -
4), Dir & sfname, True

Since sfname is strictly the name of the file with no path, there's no
reason for the Dir function call there. Try using

DoCmd.TransferText acLinkDelim, "Link Spec", Left(sfname, Len(sfname) - 4),
Dir & sfname, True

 

[G]

I'll give it a try as soon as my db comes back up. I knew it had to be
something really simple that I was missing.

 

[G]

it did not link any tables. I'll do a step through. here's the revised code:

spath = "C:\Documents and Settings\Owner\My Documents\CJ\15181\"

sfname = Dir("*.txt")

Do While Len(sfname) > 0
DoCmd.TransferText acLinkDelim, "Link Spec", Left(sfname, Len(sfname) -
4), Dir & sfname, True
'sfname = Dir
Loop
MsgBox ("files are linked")

 

[Douglas J. Steele]

Sorry, I missed the fact that you had two calls to Dir in there. You need to
refer to spath, not Dir, in the TransferText statement.

spath = "C:\Documents and Settings\Owner\My Documents\CJ\15181\"
sfname = Dir("*.txt")

Do While Len(sfname) > 0
DoCmd.TransferText acLinkDelim, "Link Spec", Left(sfname, Len(sfname) -
4), spath & sfname, True
sfname = Dir()
Loop
MsgBox ("files are linked")

 

[G]

worked like a charm! Except where the file names were too long, and I can fix
that.
Many many thanks! You're wonderful!

 

[G]

absolutely awesome!