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.