Automating the Importing of Text Files Into a Database

[exm206]

I have been using the TranferText method to automate the importing of text files into my database. I have been using this line of code:

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

My question is, is there way to prompt a user to choose the file using a "File Open" dialogue box, instead of hard-coding the path to the text file right into the macro?

Any help on this would be greatly appreciated.

 

[bat17]

check out this article

HTH

Peter

 

[dtaylor]

hi exm206
here is the code i use - not totally sure if it is the best way but it has consistently worked for me.

first the api calls (totally plagerized)
i have this in it's own module

Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
Function GetDirectory(Optional msg) As String
Dim bInfo As BROWSEINFO
Dim Path As String
Dim r As Long, x As Long, Y As Integer
' Root folder = Desktop
bInfo.pidlRoot = 0&
' Title in the dialog
If IsMissing(msg) Then
bInfo.lpszTitle = "Select a folder."
Else
bInfo.lpszTitle = msg
End If
' Type of directory to return
bInfo.ulFlags = &H1
' Display the dialog
x = SHBrowseForFolder(bInfo)
' Parse the result
Path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal Path)
If r > 0 Then
Y = InStr(Path, Chr$(0))
GetDirectory = Left(Path, Y - 1)
Else
GetDirectory = ""
End If
End Function

************************************************************
second my code for importing text files

Function GetBusDetail()

Dim sText As String
Dim filename as string

DoCmd.RunMacro "mcrSetWarningsOff"

filename = forms!Import!txtName

sText = GetDirectory & "\" & filename & ".txt"

DoCmd.TransferText acImportDelim, ImportSpecificationName, tablename, sText

DoCmd.RunMacro "mcrSetWarningsOn"

Exit Function

End Function

*************************************************************

hope this helps - sure has made my life easier

dan