Importing Text File - File/Path Name

[Bernie]

I'm using the TransferText method and it calls for
identifying the full path/file name. How do I allow the
user to open a dialog box and select the file they wish to
import. The file format is fixed and does not change;
however the path and file name will change.

Regards,
Bernie

 

[Ken Snell [MVP]]

See The ACCESS Web for one way to get this info. You can use the function as
the variable portion of the TransferText argument for the path/file name.
http://www.mvps.org/access/api/api0001.htm

 

[Bernie Vallez]

Thank you for the help. I copied this code into my db. I
entered code to a command button. I can select the file,
then am prompted to verify the path/name. I click OK but
receive Run-Time error 2498 that says I have
an "expression you entered is the wrong data type". I'm
importing a .csv file.

 

[Douglas J. Steele]

So it sounds as though the code from The Access Web is right, but your
program is doing something wrong.

Try posting the relevant code, and maybe someone will be able to spot the
problem.

 

[Bernie]

Dim strFilter As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "Access Files
(*.mda, *.mdb)", _
"*.MDA;*.MDB")
strFilter = ahtAddFilterItem(strFilter, "dBASE Files
(*.dbf)", "*.DBF")
strFilter = ahtAddFilterItem(strFilter, "Text Files
(*.txt)", "*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files
(*.*)", "*.*")
MsgBox "You selected: " & ahtCommonFileOpenSave
(InitialDir:="C:\", _
Filter:=strFilter, FilterIndex:=3,
Flags:=lngFlags, _
DialogTitle:="Hello! Open Me!")
' Since you passed in a variable for lngFlags,
' the function places the output flags value in the
variable.
Debug.Print Hex(lngFlags)

DoCmd.TransferText ,"Spec_BOMImport", "tblE_BOMImport",,,,s
trFilter

 

[Douglas J. Steele]

First of all, you're calling ahtCommonFileOpenSave, but you're not actually
saving the filename returned from that call.

You need to assign the value of the function call to a variable so that you
can use it later:

Dim strFile As String

strFile = ahtCommonFileOpenSave (InitialDir:="C:\", _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Hello! Open Me!")
Msgbox "You selected " & strFile

Then, once you've got the file, the syntax for the TransferText method is:

DoCmd.TransferText [transfertype][, specificationname], tablename,
filename[, hasfieldnames][, HTMLtablename]

That means you want:

DoCmd.TransferText ,"Spec_BOMImport", "tblE_BOMImport", strFile