(Solved) – VBA/Excel: Search and filter variable folders within windows explorer, select a folder, and import its files


Been racking my brain on this for a couple of days without much success. The goal is to be able to perform a directory search based on the selected cell in Excel and its “classification” cell below it, and return all folders in the directory whose names contain this selected information. From there, I want to be able to select a folder from that list and have its text files imported into the excel sheet next to the originally selected cell.

I’d ideally want this to work in a loop, where it’ll repeat this process four times and import the data from four different folders into new columns. All of this is for the sake of automating some data comparisons.

As I understand it, there’s no way to filter folder results in VBA’s Application.FileDialog(msoFileDialogFolderPicker) function, so I’ve been trying to figure out a workaround. Using some code from other posts on here, I’ve been able to get VBA to recreate the search function and pull it up in an explorer window, however, I don’t understand how to use this search string within a file import function.
Here’s my current code, which gets me to the filtered folder window I need:

Sub SearchExplorerForSelection()

Dim d As String
Dim searchpath As String
Dim searchlocation As String

Dim PartNumber As Range
Dim GenType As Range
' Cancel = True
d = Selection.Value

Set PartNumber = Selection 'Get desired part number from selected cell
Set GenType = PartNumber.Offset(2) 'Get PN's classification
PartNumberSearch = GenType & "*" & PartNumber 'Set full search keywords

searchpath = "search-ms:displayname=Search Results in " & GenType & "&crumb=filename:~" & PartNumberSearch
'copy string from manual search (e.g. my documents replace USERNAME)

searchlocation = " OR System.Generic.String:" & PartNumberSearch & "&crumb=location:Z:TestCalibration_Data_Generators" & GenType
If Not d = "" Then
    Call Shell("explorer.exe """ & searchpath & searchlocation, 1)
   'src: https://stackoverflow.com/questions/24376850/open-explorer-search-from-excel-hyperlink

End If

End Sub

Any help would be greatly appreciated! I’m very new to working with VBA, so I apologize if there’s anything obvious that I’m missing here.

Leave a Reply

Your email address will not be published. Required fields are marked *