Clever Geek Handbook

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

I have multiple folders containing over 1000 subfolders. I have to move some of them (about half) to other locations depending on the progress made in each folder. Progress is noted in a spreadsheet, which also provides its way. 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 an explorer window with the selected file (such file is the one following the path value + ActiveCell. Is there a way to select multiple files at once? Suppose I want to select 200 cells, so Window Explorer will open with 200 files selected?

Thanks for the help!

+3


source to share


2 answers


Unfortunately the option /select

only allows you to select one file. There is no other option that will allow you to select multiple files. You can confirm this by checking this MS article in KB

Having said that, is it possible to achieve this in VBA since the API is SHOpenFolderAndSelectItems

not available? Answer

Yes

Follow these steps.

  • Open the module and add a link to Microsoft Shell Controls and Automation

    and Microsoft Internet Controls

    as shown below

    enter image description here

  • Next, for testing purposes, we will take a folder C:\Users\Siddharth Rout\Desktop\Test1

    that has 5 csv files numbered 1 through 5 as shown below.

    enter image description here

  • Now paste the below code into the 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

+5


source


Siddharth Rout's answer is quite helpful. However, it only works on Windows platforms where the title bar is "Windows Explorer"

.

To make this work on Windows 8 / 8.1 / 10 (and not just Windows 7 in English), we could use this function definition 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
+1


source






More articles:


All Articles