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?

If you want to always process all files in a folder, why not ask the user to pick the folder instead of all the files in the folder? — Tim Williams, Aug 04 '23 at 21:06
As far as I know, you can only provide one file name. It will not be highlighted in the list but clicking Select without actually selecting that file in the list will return it back to your code. The best you can do is `sFileString = sPath & "France - Charlotte.docx"` and it needs to be a properly constructed path\file name without any extra embedded characters. Note: `Chr(31)` is not a double-quote. You want `Chr(34)`. — BobS, Aug 05 '23 at 00:20
@TimWilliams the reason is client wants to either multi-select files or by default select all. Basically, wants to view the files, which folderpicker does not allow. — sifar, Aug 05 '23 at 03:04
@BobS i know it's achievable as I had done it some time back. It had to do with manipulating the filedialog.initialfilename somehow, but can't remember now. I know highlighting is not possible. I could limit files display in folder using wildcards with initialfilename, and the names would populate automatically in filename textbox. — sifar, Aug 05 '23 at 03:06
@TimWilliams multiselect somehow doesn't set to true for msofiledialogfolderpicker nor does selecteditems show a collection of all the files. Any idea why? — sifar, Aug 05 '23 at 17:17

score 0 · Answer 1 · answered Aug 06 '23 at 23:10

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

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

1 Answers1