I am using TransferText to import a CSV file into a database. It requires that the user type in the name of the file. Does anyone know how to make this or some other command look for a file regardless of its name in a folder somewhere so that the user would simply need to put the files they want imported into that folder and the app would do the rest?

Thanks.

Hi,
use ActiveX component CommonDialog, or it's API implementation
Peko

Thank you Peko! I'll give it a try.

DBN

I'm not sure I follow the process. It sounds like you have a desire to “poll” a folder, but you liked peko’s suggestion, which is an animal of a different stripe...

Could you write a short story describing the actions of the user, and the required actions of the computer? That would be helpful.

I have a process which imports a .csv file into Access and incorporates the data into various tables. The users get this data from another process, and that process creates a file with a time/date stamp such as 20060405123456. It is always in 1 folder. Currently, I require the user to type in the time/date stamp part because the first part is always the same. I take this part and append it to the standard file name so that I know exactly what the file is called, looking for it with its full path name with a docmd.transfertext command.

Of course, the user would rather not have to type in that cryptic set of numbers. I would like to be able to have my VBA code "poll" the folder for files, and iteratively import them, if there is more than 1. Is this possible, and how would I do that?

Thanks.

Polling is pretty easy; You just set up some timer routine (a stopwatch on a form is a great way to do this).
At each time period, you read the filenames out of the predefined folder.
Having processed a file, you can move it to a different location (which is the best process, I feel)—being sure to set up your code to gracefully handle problems like trying to copy the file if a file of that name already exists—or delete it (second best), or keep track of what you have already processed, and ignore files still in the folder but already processed.
You can look for files with Dir() and a loop (see code at the bottom of this post), or you can use the FileSystemObject (which I love).

Having found a possible processing candidate, you can get the file’s date/time stamp, or you can rely on the file’s name.
You can add controls allowing the user to have a calendar/clock type interface that is more human-friendly, and convert their input to the computer-friendly string that you indicate the files carry as their name.

To use the FileSystemObject, add a reference in your project to the Microsoft Scripting Runtime (C:\WinNT\System32\scrrun.dll, on my machine). Help can provide you additional details on this really helpful class.

Does that help?

Dear BrianWren,

This sounds like just what I need. I am going to give it a try and will let you know how it turns out.

Thanks so much for responding. This new process will really improve my app.

DBN