Browse... 4

[aw23]

I am coding a form where the user can import data to the database. Is there anyway to include a browse button where the user can select the path or do they need to type it in?

Thanks

 

[lupins46]

You could use the code at

http://www.mvps.org/access/api/api0002.htm

to display a 'browse for folder' dialog.

 

[aw23]

Thank you so much. That's really helpful. I tried to give you a star but I am getting an error. That code allows me to select a folder. Is there a way to select a file in the folder?

Thanks

 

[Ed2020]

Use the ImportTextFile function to get the filepath.

This can be modified to use different file types too.




Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hWndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Public Function ImportTextFile(Optional strInitialDirectory As String, Optional strDialogTitle As String) As String

Dim FileFilter As String, FileboxTitle As String, InitialDirectory As String

FileFilter = "Text Files (*.txt)" & Chr(0) & "*.txt" & Chr(0)

If strDialogTitle = "" Then
strDialogTitle = "Import File"
End If

If strInitialDirectory = "" Then
InitialDirectory = "c:\"
Else
InitialDirectory = strInitialDirectory
End If

ImportTextFile = GetFilebox(InitialDirectory, strDialogTitle, FileFilter)

End Function

Public Function GetFilebox(strInitialD As String, strTitle As String, strFilter As String) As String

On Error GoTo ERR_GetFilebox

Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hWndOwner = Screen.ActiveForm.hwnd
OpenFile.lpstrFilter = strFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = strInitialD
OpenFile.lpstrTitle = strTitle
OpenFile.flags = 0
Dim strTemp As String

lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
GetFilebox = ""
Else
strTemp = Left$(Trim(OpenFile.lpstrFile), InStr(OpenFile.lpstrFile, vbNullChar) - 1)
If Right$(strTemp, 4) = ".txt" Then
GetFilebox = strTemp
Else
GetFilebox = strTemp & ".txt"
End If
End If

Exit_GetFilebox:

Exit Function

ERR_GetFilebox:

Select Case Err.Number

Case 2475
OpenFile.hWndOwner = 0
Resume Next
Case Else
MsgBox "Error : " & Err.Number & vbCr & vbCr & Err.Description
End Select

Resume Exit_GetFilebox

End Function

Please do not feed the trolls.....

 

[aw23]

Thanks, how do I use that? Where do I put it?

 

[Ed2020]

Copy all the code and paste it into a standard module.

Use the ImportTextFile() function to get the filepath, then use the DoCmd.TransferText method to import your file.

Ed Metcalfe.

Please do not feed the trolls.....

 

[aw23]

Thanks, but it only allows me to view txt files? Why? I need an Excel file!

 

[Ed2020]

Simmply change the filter line to:

FileFilter = "MS Excel Files (*.xls)" & Chr(0) & "*.xls" & Chr(0)

Ed Metcalfe.

Please do not feed the trolls.....

 

[aw23]

Thanks. It works great the only problem is that the extension on the files comes up as .xls.txt

Do you know why?

Tahnks

 

[Ed2020]

In the final If statement of the code I posted you need to change the reference from ".txt" to ".xls".

Ed Metcalfe.

Please do not feed the trolls.....

 

[ietprofessional]

This is a great thread! The only thing I'm needing now is the actually path of the file I select in the dialog box. Can someone tell me how to retrieve it. The reason I need the path is so I can use it in a TransferSpreadsheet.

Thanks!

 

[aw23]

Just take the value of the txtbox that the path is in.

 

[Orion45]

This worked great, Thanks!