Need help automating import with module

[Doug Stanley]

I'm trying to automate the import of a directory of 200+
text files into Access 2000. I'm trying to adapt this bit
of code I found in the KB, but it keeps telling me an
expression I entered is the wrong data type for one of the
arguments.

I'm a VBA novice. Any help appreciated.

rivate Sub Command0_Click()
Dim InputDir, ImportFile As String, tblName As String
Dim InputMsg As String

InputMsg = "Type the pathname of the folder that
contains "
InputMsg = InputMsg & "the files you want to import."
InputDir = InputBox(InputMsg)
ImportFile = Dir(InputDir & "\*.txt")

Do While Len(ImportFile) > 0
' Use the import file name without its extension
as the table
' name.
tblName = Left(ImportFile, (InStr(1,
ImportFile, ".") - 1))
DoCmd.TransferText acImportDelim, "", InputDir, _
acTable, ImportFile, tblName
ImportFile = Dir
Loop
End Sub

 

[Bas Cost Budde]

I'm trying to automate the import of a directory of 200+
text files into Access 2000. I'm trying to adapt this bit
of code I found in the KB, but it keeps telling me an
expression I entered is the wrong data type for one of the
arguments.

I'm a VBA novice. Any help appreciated.

I expect the message you get is a run time error? That is, you can run
the code, and then suddenly it stops throwing this at you?

Anyway, state as exactly as possible what message, and where/how it
occurs. As yet I cannot find a flaw in the code you posted (but that may
depend on my stomach ;-) )

 

[Doug Stanley]

Thanks for your reply. Here is the error message:

Run-time error '2498': An expression you entered is the
wrong data type for one of the arguments.

 

[Bas Cost Budde]

Your TransferText command has wrong parameters. My guess is that this
should work:
DoCmd.TransferText acImportDelim, "", tblName, ImportFile
(replace the line)

 

[Doug Stanley]

Thanks for your reply. Here's the error message:

Run-time error 2498: An expression you entered is the
wrong data type for one of the arguments.

 

[Guest]

Thanks again for the response. This time I get a different
error:

Run-time error 3011: The Microsoft Jet database engine
could not find the object <filename>.txt. Make sure the
object exists and you spell its name and the path
correctly.

 

[Bas Cost Budde]

Thanks again for the response. This time I get a different
error:

Run-time error 3011: The Microsoft Jet database engine
could not find the object <filename>.txt. Make sure the
object exists and you spell its name and the path
correctly.

Path?

..txt?

I thought you took the .txt off with the Left call before. And path?
Database objects don't have a path.

Try to view all variables you have in the offending line. While it is
still yellow, hover over them. Also check the Help for TransferText to
see if you did something "wrong".