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!

Similar question: http://stackoverflow.com/questions/9355/programatically-select-multiple-files-in-windows-explorer - (the answers are somewhat old though, and not about vba) — KekuSemau, Sep 05 '14 at 21:08
It is slightly complicated. Let me post an answer and explain it — Siddharth Rout, Sep 05 '14 at 21:16
The obvious question to me is what will be done with them once you have them selected. Why not move them instead? — n8., Sep 05 '14 at 21:28
+ 1 A very good question! I have posted a solution. You may have to refresh the page to see it :) — Siddharth Rout, Sep 05 '14 at 21:37

score 8 · Accepted Answer · edited Jun 20 '20 at 09:12

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.

Open a module and add a reference to Microsoft Shell Controls and Automation and Microsoft Internet Controls as shown below
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.
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:

enter image description here

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

Really interesting! `.Name` is `Windows-Explorer` in my case, however (Win7). Is that a typo or does it vary? — KekuSemau, Sep 07 '14 at 08:56
In Windows 10, the WebBrowser.Name property returns `File Explorer`. Replace this: `If wb1.Name = "Windows Explorer"` with `If wb1.Name = "File Explorer"`. — ChrisB, Feb 14 '20 at 22:11

score 3 · Answer 2 · edited May 23 '17 at 12:00

Siddharth Rout's answer is quite useful. Nevertheless, it works only in Windows platforms where the title of the window is "Windows Explorer".

To get this to work on Windows 8/8.1/10 (and not only Windows 7 in English), we could use this function definition of GetExplorer:

'~~> Function to find the relevant 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 UCase(wb1.FullName) = "C:\WINDOWS\EXPLORER.EXE" Then
            If LCase(wb1.Document.Folder.Self.Path) = LCase(sFolder) Then
                Set GetExplorer = wb1
            End If
        End If
    Next
End Function

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

2 Answers2