Folder Picker Issue - VBA

Question

I am using msoFileDialogFolderPicker to get the folder path and folder name by browsing and selecting the required folder, where a dialog box will open and the user have to select the folder by clicking OK in the dialog box.

If a folder is selected and OK is clicked, the full folder path is stored in the variable Folderpth. If cancel is clicked, the variable Folderpth will be blank.

My issue is, if the user clicks OK even without selecting the folder, that particular directory path is getting stored in the variable Folderpth .

Here is the code i am using,

Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)

With diaFolder
    
    diaFolder.Title = "Select the source folder"
    diaFolder.AllowMultiSelect = False
    
    If diaFolder.Show = -1 Then
    
        Folderpth = .SelectedItems.Item(1)
        
        splitting = Split(Folderpth, "\", 9)

        counter = UBound(splitting)

        foldername = splitting(counter)
        
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        
        Set objFolder = objFSO.GetFolder(Folderpth)
        
            Else
            
                MsgBox "Please select the Source folder"
                
                Folderpth = ""
                
                Exit Sub
                
            End If
        
End With

If the user browse to a folder, where its subfolder is his required folder, but without selecting the required folder if he clicks ok, the path captured will be incorrect. I want to over come capturing the folder path without selecting it.

Is it possible

By not selecting anything, you are selecting the current folder. Why would you want to prevent that? — Christofer Weber
Not sure if excel will always open the dialouge at workbook path, but if so, maybe you could do if Folderpth = Application.ActiveWorkbook.path then Folderpth = Nothing or something like that. — Christofer Weber
@ChristoferWeber That path is used again and it affects the rest of the program that's why. — Sandy
Here is an idea. When you launch the msoFileDialogFolderPicker, store the initital path in a variable. When the user click ok, match it with the new path. If the paths are same then inform user that the path is same and would he like to continue? Simple as that — Siddharth Rout
Also, since you are using With diaFolder, diaFolder.Title should probably just be .Title, and same for the others. — Christofer Weber

VBasic2008 VBasic2008 · Accepted Answer · 2020-10-27T08:36:26

Initial File Name Restriction

How I (mis)understood your question, this is not what you asked for, but it might help you to achieve the desired result. I played for a while with no result.
The value of the InitialPath variable has to be entered without the trailing path separator (\).

The Code

Option Explicit

Sub fp()
    
    Dim InitialPath As String
    InitialPath = "C:"

    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Source Folder Selection"
        .AllowMultiSelect = False
        .InitialFileName = InitialPath & Application.PathSeparator
        Debug.Print "Initial File Name: " & .InitialFileName
        If .Show <> -1 Then
        ' User pressed 'Cancel'.
            MsgBox "Next time please select the source folder"
            Exit Sub
        Else
        ' User pressed 'OK', but didn't change the Initial Path.
            Dim FolderPath As String
            FolderPath = .SelectedItems.Item(1)
            If Right(InitialPath, 1) = ":" Then
                InitialPath = InitialPath & Application.PathSeparator
            End If
            If FolderPath = InitialPath Then
                MsgBox "'" & FolderPath & "' is not allowed."
                Exit Sub
            End If
        End If
    End With
    
    ' User pressed 'OK' selecting a path different than Initial Path.
    ' Continue...
    Debug.Print "FolderPath: " & FolderPath
    
    ' Either:
    Dim SplitArray As Variant
    SplitArray = Split(FolderPath, Application.PathSeparator)
    Dim FolderName As String
    If UBound(SplitArray) = 1 And SplitArray(0) = "" Then
        FolderName = SplitArray(0)
    Else
        FolderName = SplitArray(UBound(SplitArray))
    End If
    Debug.Print "Split: " & FolderName
    
    'or:
    With CreateObject("Scripting.FileSystemObject")
        FolderName = .getFolder(FolderPath).Name
    End With
    Debug.Print "FileSystemObject: " & FolderName
    
End Sub

Folder Picker Issue - VBA

1 Answers

Initial File Name Restriction