Store File or Folder Location in Text Field

[Mke85]

Good Morning,

I am attempting to store a file or folder location in a text field from a dialog box. I would like the ability to choose lets say a .pdf file and store the file path in a text field or just choose a folder and store that folders file path in a text field.

Here is my code so far:

Private Sub Electronic_File_Location_DblClick(Cancel As Integer)
' Requires reference to Microsoft Office 11.0 Object Library.
Dim FileDialog As FileDialog
Dim Filelocation As String
Set FileDialog = Application.FileDialog(msoFileDialogFilePicker)
With FileDialog
.AllowMultiSelect = False
.Title = "Please select one file"
If .Show = True Then
selectedFile = FileDialog.SelectedItems(1)
[Electronic File Location].Value = selectedFile
End If
End With
End Sub

With this code I am forced to select a file directly and it will store the path in my text field.

If I change the If statement to:

If .Show = True Then
[Electronic File Location].Value = .InitialFileName
End If

I still have to choose a file directly but it will only store the parent directory path in the text field.

Is there a way to choose a folder and just store its path or choose a file and store its path in a text field?

Thanks for the help,

 

[MajP]

Without a lot of coding, I do not think there is a combination file or folder browser. So the easy workaround would be to prompt the user asking if they want to choose a file or folder.

Public Sub FileOrFolder()
  Dim rsp As Long
  Dim strFileOrFolder As String
  'I would make a custom form with a radio button to pick which one to choose
  'file or folder. For demo I will use a msgbox
  rsp = MsgBox("Select Ok to Choose a File, Cancel for selecting only a folder", vbOKCancel, "Select File?")
  If rsp = vbOK Then
    strFileOrFolder = getfile
  Else
    strFileOrFolder = getFolder
  End If
  If strFileOrFolder <> "" Then
    If rsp = vbOK Then
      MsgBox "Your File name is: " & strFileOrFolder
    Else
      MsgBox "Your Folder name is: " & strFileOrFolder
    End If
  End If
 End Sub

Public Function getfile() As String
' Requires reference to Microsoft Office 11.0 Object Library.
Dim FileDialog As FileDialog
Dim Filelocation As String
Set FileDialog = Application.FileDialog(msoFileDialogFilePicker)
With FileDialog
 .AllowMultiSelect = False
 .Title = "Please select one file"
 If .Show = True Then
   getfile = FileDialog.SelectedItems(1)
 End If
End With
End Function

Public Function getFolder(Optional InitialPath As String = "") As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    If Not InitialPath = "" Then .InitialFileName = InitialPath
    If .Show = True Then getFolder = .SelectedItems(1)
End With
End Function

 

[Mke85]

Thanks for the idea, it was worked out well so far.