Solved Getting a directory path (1 Viewer)

[PaquettePaul]

I have a form that sends PDFs and excel spreadsheets to a given directory path location. Currently, I have the path stored in a configuration field in the database. However, I would like to provide the user with the option of browsing through the directory until they find where they want the file to be stored.

i have tried using the Application.FileDialog(msoFileDialogFilePicker).Selected Items(1) approach to obtain the directory path but a) you need to select a file to get the path and, when I do, b) the contents of the SelectedItems is the full path along with the file name. I only want the directory path.

wondering what I am doing wrong or is there another way. I could always just use an empty field that they type in but that is pretty basic.

thanks

 

[Pat Hartman]

Here are two useful functions. One opens the file dialog to select a file, the second opens the dialog to select a folder. So, use fChooseDirectory() to pick just the directory name.

Option Compare Database
Option Explicit

Public Function fChooseFile()
 
   ' Requires reference to Microsoft Office 11.0 Object Library.
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
 
   ' 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"
 
      'starting location
      .InitialFileName = CurrentProject.path
      
      ' Clear out the current filters, and add our own.
      .Filters.Clear
''''      .Filters.Add "Excel ", "*.XLSX"
      .Filters.Add "Access Databases", "*.ACCDB, *.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
         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
            fChooseFile = varFile
''''            Me.FileList.AddItem varFile
         Next
        
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

End Function


''https://msdn.microsoft.com/en-us/library/hww8txat%28v=vs.84%29.aspx?f=255&MSPPError=-2147217396
''https://www.exceltrick.com/formulas_macros/filesystemobject-in-vba/
''https://bettersolutions.com/vba/files-directories/file-system-object.htm
''http://www.xl-central.com/list-files-fso.html
''http://www.thevbprogrammer.com/ch06/06-09-fso.htm


Public Function fChooseDirectory()

    ' requires a reference to the Office xx Object library
    'Declare a variable as a FileDialog object.
    'Dim fd As FileDialog

   '''' Const msoFileDialogFolderPicker = 4 'use for late binding
    
    Dim fd As Object
    
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        '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

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.

                'Only one item will be returned since the file dialog is a folder picker
                'MsgBox "The path is: " & vrtSelectedItem
                fChooseDirectory = vrtSelectedItem
                Exit Function
            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing
    fChooseDirectory = "Error - nothing chosen"
End Function

 

[PaquettePaul]

That did the trick. Thanks a bunch

 

[Pat Hartman]

You're welcome