VBA to open file dialog and store path in text box

[tmyers]

I have been having some issues with this one.

I made a text box and bound it to my field [FilePath]. I am trying to work out a button that when clicked, will open the file dialog window and let my users select a file and input the files path into the text box. I will then make an on double click event to open said file path.

I tried https://answers.microsoft.com/en-us...g-access/374b9f15-77c3-4348-bf75-676658c9bb6b, but the answer there cause a slew of errors the moment the code was put in, completely breaking the form in which the button resided (really weird).

So, what kind of code would I need to open a window, select a file, and store its path in my text box? I prefer to store the folder directory if possible. I know file dialog doesnt work for folders.

Could someone offer insight on this?

 

[theDBguy]

I know file dialog doesnt work for folders.

FileDialog should work for Folders too if you use the msoFileDialogFolderPicker constant.

 

[tmyers]

I stumbled across:

Private Sub DrawingLink_Click()

  Dim f As Object
  Dim strFile As String
  Dim strFolder As String
  Dim varItem As Variant

  Set f = Application.FileDialog(3)
  f.AllowMultiSelect = False
  If f.Show Then
    For Each varItem In f.SelectedItems
        strFile = Dir(varItem)
        strFolder = Left(varItem, Len(varItem) - Len(strFile))
        MsgBox "Folder: " & strFolder & vbCrLf & _
            "File: " & strFile
        Me.Link = strFolder & "\" & strFile
    Next
  End If
  Set f = Nothing
End Sub

This worked for one specific file. How could I modify that to add in the msoFileFolderPicker like you suggested?

 

[theDBguy]

I stumbled across:

Private Sub DrawingLink_Click()

  Dim f As Object
  Dim strFile As String
  Dim strFolder As String
  Dim varItem As Variant

  Set f = Application.FileDialog(3)
  f.AllowMultiSelect = False
  If f.Show Then
    For Each varItem In f.SelectedItems
        strFile = Dir(varItem)
        strFolder = Left(varItem, Len(varItem) - Len(strFile))
        MsgBox "Folder: " & strFolder & vbCrLf & _
            "File: " & strFile
        Me.Link = strFolder & "\" & strFile
    Next
  End If
  Set f = Nothing
End Sub

This worked for one specific file. How could I modify that to add in the msoFileFolderPicker like you suggested?

Change one line to this and try it.

Set f = Application.FileDialog(4)

 

[tmyers]

That did the trick!
As always, thanks for your help!

 

[theDBguy]

That did the trick!
As always, thanks for your help!

You're welcome. Good luck with your project.

 

[vhung]

Thanks for this code; I make use of it... for file path name

Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant

Set f = Application.FileDialog(1)
f.AllowMultiSelect = False

If f.show Then
For Each varItem In f.SelectedItems
strFile = Dir(varItem)
strFolder = Left(varItem, Len(varItem) - Len(strFile))
Me.[LinkAccdb] = strFolder & "" & strFile
Next
End If

 

[theDBguy]

Thanks for this code; I make use of it... for file path name

Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant

Set f = Application.FileDialog(1)
f.AllowMultiSelect = False

If f.show Then
For Each varItem In f.SelectedItems
strFile = Dir(varItem)
strFolder = Left(varItem, Len(varItem) - Len(strFile))
Me.[LinkAccdb] = strFolder & "" & strFile
Next
End If

Hi. Glad to hear you found it useful. Cheers!

 

[vhung]

Hi. Glad to hear you found it useful. Cheers!

Yeah