Importing Text file

[crh]

I have created a macro to import a txt file and it works fine, however the issue i have is that the filename will change on a daily basis as the system that produces the txt file assigns it a chronological order and appends to the filename each day. So today my file is named xxx0123.txt but tomorrow it will be called xxx0124.txt. Of course, the macro now falls over. I have tried using a wildcard, e.g. xxx*.txt, but access wants to find the exact filename. Can anyone advise how i can update the macro to either ask the user for input (e.g. a browse window) or how i can use something like a wildcard? Much appreciated.

 

[FoFa]

Create a function or sub that Uses DOCMD.Transfertext and pull the filename from a form you create.

 

[crh]

Thanks Fofa. To be honest i am so new at this, it's scary. If i might ask, can you suggest an example please? I am thinking that i could also use a table to store the filenames? Or is using a form better? Thanks a million for your help with this.

 

[FoFa]

DoCmd.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

So you can use this in VBA, and pull the FileName (or any of the fields) from somewhere else. If you had it on a form you can use the FORMS!YourFOrmName!YourTextBoxName to pull that from the field. Use HELP (in VBA editor, use VB Help) to look up DoCmd. Typically I use the first 4 or 5 fields only.

 

[ghudson]

Here is a Browse [Find a directory or file] sample.