Browsing for a file and return the path and filename

[darkhat01]

I am having problems on how I can browse for a file and return the path of the file selected by the user.

Example a user will be in Access on a Form, click the Browse for File Command button, locate a file on the C: drive in a folder called test2 and the file name is test.csv.

I would want it to return back to the form in a text box C:\test2\test.csv

Here is the code that I have started but not working:

Private Sub GetFilePath_Click()

Dim sMyPath As String
Dim txtPathName As String

sMyPath = Application.GetOpenFilename _
(filefilter:="Comma Separated Value (*.csv),*.csv|Access Files (*.txt)|*.txt", _
Title:="Select Your File", MultiSelect:=False)

If sMyPath = False Then Exit Sub

txtPathName = sMyPath
End Sub

txtPathName is a text box on the Form….

Any help would be great…

Thanks,

Darkhat

 

[PHV]

GetOpenFilename is a method of the Excel.Application object, not of the Access.Application object !
In ac2003 (and perhaps acXP) you may use the Application.FileDialog object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[darkhat01]

Ok so here is what I am doing now but still getting errors any ideas??:

Dim sMyPath As FileDialog

' Set up the File Dialog.
Set sMyPath = Application.FileDialog(msoFileDialogFilePicker)

With sMyPath

' Allow users to not make multiple selections in dialog box
.AllowMultiSelect = False

' Set the title of the dialog box.
.Title = "Select your File"

' Clear out the current filters, and add our own.
.Filters.Add "Comma Separated Value", "*.csv"
.Filters.Add "Text File", "*.txt"
.Filters.Add "All Files", "*.*

 

[darkhat01]

Ohh and I did turn on the Microsoft Office 11.0 Object Library In the VBA Code Tools menu, References...

 

[PHV]

but still getting errors
Which errors ? Where in the code ?


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[darkhat01]

I did not close the with, that was why I was getting the error.

Now I am getting an error with the .SelectedItems this is where the user presses the action button. i run the code and it selects the .SelectedItems and gives the error: Compile Error: Argument not optional. I thought that .SelectedItems is an option? Any ideas????


Private Sub getFilePath()

'Declare a variable to contain FileDialog
Dim sMyPath As FileDialog
'Declare a variable to contain the path
Dim sPath As String

' Set up the File Dialog.
Set sMyPath = Application.FileDialog(msoFileDialogFilePicker)

With sMyPath

' Allow users to not make multiple selections in dialog box
.AllowMultiSelect = False

' Set the title of the dialog box.
.Title = "Select your File"

' Clear out the current filters, and add your own.
.Filters.Add "Comma Separated Value", "*.csv"
.Filters.Add "Text File", "*.txt"
.Filters.Add "All Files", "*.*"

'Set the text of the button Caption
'.strButtonCaption = "Get File"

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
sPath = .SelectedItems
'Show the Path that is selected in a message box
MsgBox "The path is: " & sPath
Else
End If

End With

End Sub


Thank you,

darkhat

 

[PHV]

Place the cursor inside the .SelectedItems word and press the F1 key.

 

[darkhat01]

I finally got it to work here is what I did:

Private Sub getFile_Click()
'Declare a variable to contain FileDialog
Dim sMyPath As FileDialog
'Declare a variable to contain the path
Dim sPath As Variant


' Set up the File Dialog.
Set sMyPath = Application.FileDialog(msoFileDialogFilePicker)

With sMyPath

' Allow users to not make multiple selections in dialog box
.AllowMultiSelect = False

' Set the title of the dialog box.
.Title = "Select your File"

' Clear out the current filters, and add your own.
.Filters.Add "Comma Separated Value", "*.csv"
.Filters.Add "Text File", "*.txt"
.Filters.Add "All Files", "*.*"

'Set the text of the button Caption
'.strButtonCaption = "Get File"

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
For Each sPath In .SelectedItems
'Show the Path that is selected in a message box
MsgBox "The path is: " & sPath
'Pass string back to form
Me!txtFileLocation = sPath
Next sPath
'Show if Canceled is selected in a message box
Else
sPath = "No File Selected to Import."
MsgBox sPath
'Pass string back to form
Me!txtFileLocation = sPath
End If

End With

End Sub

Thanks for your help PHV

 

[fumei]

Please try and use the code window for posting code. Thanks.

Gerry
My paintings and sculpture

 

[darkhat01]

Still new here how do i use the code window?

 

[darkhat01]

I figuered it out....

 This is a test