Solved DoCmd.TransferText not working

[ProgramRasta]

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.

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]

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]

Is the SpecificationName valid?

From the docs:

Thanks for replying to the thread.

I have found the issue (but not a fix)

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]

I think you will need Dir("Path/To/FileName*")

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

 

[ProgramRasta]

I think you will need Dir("Path/To/FileName*")

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

Thank you for your time this morning.

Have a nice day.

 

[cheekybuddha]

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]

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.