Solved DoCmd.TransferText not working (1 Viewer)

ProgramRasta

Member
Local time
Today, 12:57
Joined
Feb 27, 2020
Messages
98
Hi All

I have a very frustrating issue and I am unable to understand why it's happening.

I have the following code that imports a .csv file into a database table with the format I want.

Code:
DoCmd.TransferText TransferType:=acImportDelim, _
                SpecificationName:="ImportSpecification", _
                TableName:="tbl_Importtable", _
                FileName:=ImportFileName, _
                HasFieldNames:=True

However, when I initially run the code I get an 'invalid argument' error.

I need to actually attempt to import the file manually into the database, cancel the import and then the code runs normally.

Anyone any ideas on why this occurs and hopefully provide some guidance on a fix?

Many thanks
 

cheekybuddha

AWF VIP
Local time
Today, 12:57
Joined
Jul 21, 2014
Messages
2,237
Is the SpecificationName valid?

From the docs:
A string expression that's the name of an import or export specification you've created and saved in the current database. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file.

To create a schema file, you can use the text import/export wizard to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.
 

ProgramRasta

Member
Local time
Today, 12:57
Joined
Feb 27, 2020
Messages
98
Is the SpecificationName valid?

From the docs:
Thanks for replying to the thread.

I have found the issue (but not a fix)

Code:
ImportFileName = FilePath & Dir("FileName*")

The ImportFileName is only recognising the FilePath and not the Dir("FileName*") unless I do the manual import.

The reason I use Dir("*") is I'm unsure of the full file name on import but the starting letters of the file name will never change.
 

cheekybuddha

AWF VIP
Local time
Today, 12:57
Joined
Jul 21, 2014
Messages
2,237
I think you will need Dir("Path/To/FileName*")

When using the wildcard how will you know you are importing the right file?
 

cheekybuddha

AWF VIP
Local time
Today, 12:57
Joined
Jul 21, 2014
Messages
2,237
No problem!

If you want to import all the files test match the pattern then you will need to loop calling Dir() until it returns an empty string.

If you are only looking for a single file then you may need some extra checks to make sure you have the right one, as Dir() will return the first match it finds, which may not be the file you are expecting!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:57
Joined
Feb 19, 2002
Messages
42,981
To summarize, TransferText et al will only accept actual file names. You cannot use wildcards and expect them to figure out what file to enter.
 

Users who are viewing this thread

Top Bottom