Auto-select and Highlight all files from selected folder in Application.FileDialog(msoFileDialogFilePicker)

Question

I am using Application.FileDialog(msoFileDialogFilePicker) and navigating to any specific folder to automatically select (and highlight if possible) all files in that folder, populating the Filename textbox in Application.FileDialog as shown below (here i have only manually selected 2 files for populating the filename textbox:

I tried:

Sub SelectAllFilesInFolder()
Dim fd As FileDialog
Dim vSelectedItems As Variant
Const sPath As String = "C:\Users\somefolder\"
Dim sFileString As Variant
Dim vFiles As Variant
Dim FileColl As Collection

With ThisWorkbook
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = True
        .Filters.Add "Word Files", "*.doc*", 1
        .InitialFileName = sPath
        .ButtonName = "Select"
        sFileString = sPath & Chr(31) & "France - Charlotte.docx" & Chr(31) & " " & Chr(31) & "France - Fabienne.docx" & Chr(31)
        .InitialFileName = sFileString
                
        If .Show = -1 Then
           ' some processing code    
        End If
        
    End With

End With
End Sub

However, initialFilename is not populating the filename textbox. Even if i manually enter the following string in it, it gives an error on clicking Open button.

"France - Charlotte.docx" "France - Fabienne.docx"

It seems the string that gets populated when user manually selects files in folder, is different somehow. How can this be automated?

Answer 1

This VBA is a workaround that allows a user to select a folder and then copies files that match specific names, from the selected folder to a temporary directory. After viewing the files in the temporary directory, the user is prompted to either leave the temporary directory or to delete it. I didn't understand the point of highliting all the filed in a directory and not just part of them.

The approach of copying files to a temporary directory was chosen because selecting more then one file with the explorer /select command did not work directly for multiple files.

I think this approch provides a clean way of isolating and displaying files of interest. Moreover, by providing the user with the option to delete the temporary directory after viewing, we ensure that we don't inadvertently clutter their file system with leftover files and folders.

Sub selectedFilescopy2TempFolder()
    Dim strFolderPath As String
    Dim searchStrings() As String
    Dim tempDir As String
    Dim fileName As String
    Dim fileWithoutExtension As String
    Dim i As Integer
    Dim matchedFiles() As String
    Dim matchedFileCount As Integer
    Dim FSO As Object
    Dim userResponse As VbMsgBoxResult

    searchStrings = Split("1111,googleTranslate2,AnotherName", ",")

    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then
            strFolderPath = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With

    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    tempDir = strFolderPath & "\TempSelectDir"
    If Not FSO.FolderExists(tempDir) Then
        FSO.CreateFolder(tempDir)
    End If

    fileName = Dir(strFolderPath & "\*.*")
    Do While fileName <> ""
        
        fileWithoutExtension = Left(fileName, InStrRev(fileName, ".") - 1)
        
        For i = LBound(searchStrings) To UBound(searchStrings)
            If fileWithoutExtension = searchStrings(i) Then
                FSO.CopyFile strFolderPath & "\" & fileName, tempDir & "\" & fileName
                
                matchedFileCount = matchedFileCount + 1
                ReDim Preserve matchedFiles(1 To matchedFileCount)
                matchedFiles(matchedFileCount) = fileName
            End If
        Next i
        
        fileName = Dir
    Loop

    If matchedFileCount > 0 Then
        Shell "explorer " & tempDir, vbNormalFocus
        userResponse = MsgBox("If you wish to leave the folder as is, press OK. If you want to delete the folder, press Cancel.", vbOKCancel + vbQuestion)

        If userResponse = vbCancel Then
            For i = 1 To matchedFileCount
                FSO.DeleteFile tempDir & "\" & matchedFiles(i)
            Next i
            FSO.DeleteFolder tempDir
        End If

    Else
        MsgBox "No matching files found.", vbInformation
    End If

    Set FSO = Nothing
End Sub