Prompt user for filename.

[cwhite23]

Hi,

I need help from some of you gurus out there. I have a database that prints out our shipping manifests each morning. The problem is, the filename for the data that the database uses changes every day. Is there any way through VBA (or even a macro)to prompt the user for the filename so that the database will import the data automatically, ie, by the user simply just clicking on a button and giving the appropriate date information? The filename is in the format 'MMMDD_03.cs'. What I need is for the system to prompt for the MMMDD part, as in 'SEP22' The system would then be able to read this into the filename and import the 'SEP22_03.cs' file automatically. Any help would be appreciated.

 

[Tranman]

cwhite23,
If you're using a pre-defined import spec, then you can use the TransferText method of the DoCmd object. One of the arguments for this method is the file name. In order to have the program ask for the file name in a module, just use the InputBox function:

<stringname> = InputBox &quot;Please enter file date.&quot;

Then put stringname together with the rest of the file name and use that in the TransferText.

So it would be something like:

Public Function ImportTxtFile()
Dim strDate as String
dim strFile as String
strDate = InputBox &quot;Please enter file date.&quot;

strFile = strDate & &quot;_03.cs&quot;

DoCmd.TransferText acImportFixed, <mySpecName>, <destTableName>, strFile, <booleanHasFieldName--True or False>
End Function

'That hasfieldnames has to do with whether the first record of the data file contains the names of the fields or not.

Good Luck,
Tranman

 

[cwhite23]

Perfect!!! Thanks for the help!