How can I automate the importing of text files into access?

[Tim Few]

I have been trying to write a macro that will automate the transfer of data
from text files into tables in access. So far I have found two ways of doing
it, but neither are exactly what I am looking for.

The first way is to use the sendkeys function to open the get external data
option, but this means that users will have to go through several menus and
this is what I'm trying to avoid.

The other is to use the Transfer text function, but this seems to limit me
to opening one file, I tried to have a text box that users could enter the
file path into, but access doesn't acept this.

Is there any way of importing the data as with the transfer text function,
without having to specify the exact path (maybe just specifying the folder
and bringing up a box that lets the user choose the file they want to import?

Thanks in advance for your help.

Tim

 

[Ken Snell [MVP]]

If you want to let the user navigate to a file, then you cannot do this with
just a macro. You'll need to use VBA code to display the navigation browse
window.

The ACCESS Web has VBA code that displays such a window:
www.mvps.org/access/api/api0001.htm

You would need to put the code in a regular VBA module, then you can call
the function from the TransferText macro by putting the function name (with
the parentheses) in the File Name argument box. (You may need to put = sign
in front of the function name, too.)

 

[Seikyo]

How can we put this module into a transfertext macro?

I tried this but it say "Cannot import this file"

ahtCommonFileOpenSave(Filter:="Text Files (*.text)", "*.txt",
OpenFile:=True,DialogTitle:="Please select an input
file...",Flags:=ahtOFN_HIDEREADONLY)