putting the path from FileDialog into a text box

[miconian]

So, what I want to do is, allow the user to browse the hard drive, pick a file, and then have the path to that file appear in a text box.

I found a popular solution that does something similar: it allows the user to choose multiple files, and it puts the paths for those files in a listbox. But I don't want to allow multiple choices. So I have attempted to modify the code to meet my purposes.

The original code that I'm referencing appears here:
http://msdn.microsoft.com/en-us/library/bb243865(v=office.12).aspx

Here is my modification. I am using a button called cmdText and a text box called txtPath.

Private Sub cmdText_Click()

   Dim fDialog As Office.FileDialog
   Dim varFile As Variant

   ' Clear listbox contents. '
   Me.txtPath.Value = ""

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

   With fDialog

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

      ' Set the title of the dialog box. '
      .Title = "Please select one file"

      ' Clear out the current filters, and add our own.'
      .Filters.Clear
      .Filters.Add "Access Databases", "*.MDB"
      .Filters.Add "Access Projects", "*.ADP"
      .Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method returns True, the '
      ' user picked at least one file. If the .Show method returns '
      ' False, the user clicked Cancel. '
      If .Show = True Then

     'add selected path to text box
     Me.txtPath.Value = .SelectedItems

      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With
End Sub

Currently I'm getting a runtime error on this line:

Me.txtPath.Value = .SelectedItems

On that line, I am attempting to put the path into the text box. I'm obviously a bit clumsy with VBA in general, so I'm sure I'm missing some obvious step. Thanks!

 

[RJPotts]

(Edit: Your message came up while I was searching for something else, I didnt notice the fact it was Access until I looked into the link you'd posted too. The code below is for Excel, I'm afraid I have no idea how much, if any, of it will work in access)

Hi,

I think this should do what you need;

Private Sub CmdText_Click()

    Dim SelFileName As String
    
    SelFileName = Application.GetOpenFilename(Title:="Please select a file")
    
    If SelFileName <> “False” Then
        txtPath.Value = Left$(SelFileName, InStrRev(SelFileName, "\"))
    End If
       
End Sub

Best regards
Richard

 

[miconian]

Apparently, there is no GetOpenFilename method in the Access Application object.

I think I'm pretty close using FileDialog above. I just don't know how to assign the path the user chooses to a variable and therefore to the text box.

 

[mjrofra]

Hi,

I´m not sure, but you can try this:

Me.txtPath.Value = .SelectedItems[COLOR=#0000FF](1)[/COLOR]

 

[miconian]

Hey, thanks. That actually does seem to work.

I had a similar idea earlier, and tried to use .SelectedItems(0), but that didn't work. Why is the array's only item assigned to (1) and not (0)?