importing with forms/macros

  • Thread starter Thread starter HX
  • Start date Start date
H

HX

Hello,

Currently I have some forms & macros in place that allow a user to import a
text file to the database. The problem, however, is that the user is forced
to put the file in a particular directory, and rename it to a specific name
so that the transfertext function used in the macro will be able to find and
import the text file (using a spec, also).

How can I change this so that the user can just FIND and SELECT the file
they want to import using a window similar to the File/Open window? Of
course, I still need to specify the rest of the import details, like the
spec to use and the name of the file to import to ... I just want the user
to be able to point to the file instead of having to verify the location and
name of the file so that it matches what transfertext is looking for.

I'm a novice with modules and vb, which I'm sure isn't going to be very
helpful in this case. : (

Thanks for any pointers.
 
You're right: it will involve using VBA instead of macros.

There's ready-made VBA code at www.mvps.org/access/api/api0001.htm to
invoke the standard Windows file save dialog. Once you've got that into
a module and made sure it's working (e.g. by using the TestIt() function
that's included, all you have to do is to replace the macro with a
button on the form and VBA code in the button's Click event procedure,
something like this:

Dim strFilter As String
Dim strInputFileName as string

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

DoCmd.TransferText XXXX

Replace the XXXX with the arguments you're using now for the
TransferText macro, except for using
strInputFileName
(without quotes) instead of the actual path and name.
 
Finally got around to this -- that was PERFECT! And your help in providing
the extra details (what info to fill in, and where) is greatly appreciated -
made implementing this a snap, being I'm really unfamiliar with VBA and
modules. Thanks!
 
One question....

If the user clicks CANCEL on the Open dialog box, a runtime error is
displayed asking whether to end or debug, then the macro error window
displays. How can I disable this, perhaps run another macro (since that's
easier for me to work with) which will pop up another form/window indicating
the import failed?
 
Back
Top