How to select multiple files in Windows Explorer from selected cells in Excel using VBA?

Question

I have several folders containing over 1000 subfolders each. I have to move some of them (about half) to other locations depending on the progress made within eachsubfolder. The progress is notated in a spreadsheet, which also provides its path. I have the following code:

    Sub open_explorer()
    Shell "C:\Windows\explorer.exe /select, K:\user\folder\A\" & ActiveCell.Value, vbMaximizedFocus
    End Sub

So, this code will open a window explorer with a file selected (such file is the one that follows the path + ActiveCell value. Is there a way to select multiple files at once? Lets say I want to select 200 cells, so Window Explorer will open with the 200 files selected?

Thank you for the help!

Answer 1

Unfortunately /select option will only let you select single file. There is no other option which will let you select multiple files. You may confirm that by checking this MS KB Article

Having said that, is it possible to achieve that in VBA since the API SHOpenFolderAndSelectItems is not available? The answer is

YES

Follow these steps.

1. Open a module and add a reference to Microsoft Shell Controls and Automation and Microsoft Internet Controls as shown below

   

2. Next for testing purpose, we will take the folder C:\Users\Siddharth Rout\Desktop\Test1 which has 5 csv files numbered from 1 to 5 as shown below.

   

3. Now paste the below code in a module and run the procedure Sub Sample()

Code:

Option Explicit

Sub Sample()
    SelectMultipleFiles "C:\Users\Siddharth Rout\Desktop\Test1"
End Sub

Sub SelectMultipleFiles(sFolder As String)
    Dim wb As WebBrowser
    Dim objExp As Shell32.Shell
    
    Set objExp = New Shell32.Shell
    
    objExp.Open sFolder
    
    '~~> Find our explorer window
    Do While wb Is Nothing: Set wb = GetExplorer(sFolder): Loop
    
    '~~> We are going to select files 1,3 and 5.csv
    '~~> The 5& is used so that any previous selections are cleared off
    Call wb.document.SelectItem(sFolder & "\1.csv", 5&)
    Call wb.document.SelectItem(sFolder & "\3.csv", 1&)
    Call wb.document.SelectItem(sFolder & "\5.csv", 1&)
End Sub

'~~> Function to find the releavnt explorer window
Function GetExplorer(sFolder As String) As WebBrowser
    Dim objExp As New Shell32.Shell
    Dim wb1 As WebBrowser

    For Each wb1 In objExp.Windows
        If wb1.Name = "Windows Explorer" And _
        LCase(wb1.document.Folder.Self.Path) = LCase(sFolder) Then
            Set GetExplorer = wb1
        End If
    Next
End Function

Output:

NOTE: As mentioned by @ChrisB, in Windows 10, the WebBrowser.Name property returns File Explorer instead of Windows Explorer so to make it compatible for both versions you can use

If wb1.Name = "Windows Explorer" or wb1.Name = "File Explorer"....

Alternatively you can use WMI to find the Windows version and then choose between Windows/File Explorer