Private Sub CommandButton1_Click()
Dim d As String
Dim searchpath As String
Dim searchlocation As String
Cancel = True
d = TextBox1.Value
'change window name to make sure new explorer window is opened for each instance
'copy string from manual search
searchpath = "search-ms:displayname=" & d & "%20Results%20&crumb=System.Generic.String%3A"
'copy string from manual search (e.g. my documents replace USERNAME)
searchlocation = "&crumb=location:C%3A%5CUsers%5CUSERNAME%5CDocuments"
If Not d = "" Then
Call Shell("explorer.exe """ & searchpath & d & searchlocation & "", 1)
End If
End Sub
Thanks for the help Hans. The following code does populate the listbox with all the contents of the folder. How should I emmbed this code with the above code so that when the command button is clicked it loads the listbox with the search value.
Dim myfso As FileSystemObject, myfolder As Object, myfile As Object
Me.ListBox1.Clear
Set myfso = New Scripting.FileSystemObject
Set myfolder = myfso.GetFolder("\\server\my folder name")
For Each myfile In myfolder.Files
Me.ListBox1.AddItem myfile.Name
Next myfile
Private FileFilter As String
Function ListFiles(ByVal FolderPath As Variant, ByRef OutputCell As Range, Optional ByVal SearchDepth As Long)
Dim n As Long
Dim oFile As Object
Dim oFiles As Object
Dim oFolder As Variant
Dim oShell As Object
If oShell Is Nothing Then
Set oShell = CreateObject("Shell.Application")
End If
Set oFolder = oShell.Namespace(FolderPath)
If oFolder Is Nothing Then
MsgBox "The Folder '" & FolderPath & "' Does Not Exist.", vbCritical
SearchDepth = 0
Exit Function
End If
Set oFiles = oFolder.Items
n = 0
oFiles.Filter 64, FileFilter
For Each oFile In oFiles
OutputCell.Offset(n, 0) = oFolder.self.Name
OutputCell.Offset(n, 1) = oFile.Name
'OutputCell.Parent.Hyperlinks.Add OutputCell.Offset(n, 1), oFile.Path, , , oFile.Name
n = n + 1
Next oFile
Set OutputCell = OutputCell.Offset(n, 0)
oFiles.Filter 32, "*"
If SearchDepth <> 0 Then
For Each oFolder In oFiles
Call ListFiles(oFolder, OutputCell, SearchDepth - 1)
Next oFolder
End If
End Function
Sub ListFilesTest()
Dim lastRow As Long
Dim Rng As Range
Dim Wks As Worksheet
FileFilter = InputBox("Enter the Full or Partial name of the Files to Find.")
If FileFilter = "" Then Exit Sub
FileFilter = "*" & FileFilter & "*"
Application.ScreenUpdating = False
Sheets.Add , Sheets(Sheets.Count)
ListFiles "K:\Downloads", Sheets(Sheets.Count).Range("A1"), 1
sn = Sheets(Sheets.Count).Cells(1).CurrentRegion.Value
Application.DisplayAlerts = False
Sheets(Sheets.Count).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
With UserForm1
.ListBox1.List = sn
.Show
End With
End Sub
Thanks for the Help Hans. With your line of code here's how I have modified the code. I've placed textbox in userform with the name SearchTextBox and added a button. Following is the button code. The goal is when the user writes the search value in the textbox and hit the button the related data will get filtered in the listbox.
Private Sub CommandButton3_Click()
Dim lastRow As Long
Dim Rng As Range
Dim Wks As Worksheet
'Set Wks = Worksheets("Sheet1")
'Set Rng = Wks.Range("A1:C1")
'lastRow = Wks.Cells(Rows.Count, "A").End(xlUp).Row
'FileFilter = InputBox("Enter the Full or Partial name of the Files to Find.")
FileFilter = UserForm1.SearchTextBox
If FileFilter = "" Then Exit Sub
FileFilter = "*" & FileFilter & "*"
Application.ScreenUpdating = False
Sheets.Add , Sheets(Sheets.Count)
ListFiles "K:\Downloads", Sheets(Sheets.Count).Range("A1"), 1
sn = Sheets(Sheets.Count).Cells(1).CurrentRegion.Value
Application.DisplayAlerts = False
Sheets(Sheets.Count).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
With UserForm1
.ListBox1.List = sn
'.Show
End With
End Sub
I have comment out the line .show from the above code as whenever I click the button I get error message. After removing the line the code works but it shows all the contents in the folder. does not limit the search results to search value.
If the commandbutton uis on the userform, it makes no sense to show the userform in its code - when the user clicks the button, the form must already have been shown.
You declared fikeFilter as Private in the List_Files_Matching_Input_Name module, so it was not known in the userform module.
Since you didn't use Option Explicit at the top of your modules, this error was not flagged.
It would help if you indented your code consistently instead of randomly.
Search Folder.xlsm
You do not have the required permissions to view the files attached to this post.
However, I'm wondering what the lines sheets.Count do? I'm not using any sheets in the workbook. All I need is load the listbox with the SeachTextBox Values. Would be glad if I get any explanation on this.
Private Sub SearchTextBox_AfterUpdate()
Dim sn As Variant
FileFilter = Me.SearchTextBox
If FileFilter = "" Then Exit Sub
FileFilter = "*" & FileFilter & "*"
Application.ScreenUpdating = False
Sheets.Add , Sheets(Sheets.Count)
If ListFiles("K:\Downloads", Sheets(Sheets.Count).Range("A1"), 1) = False Then
sn = Sheets(Sheets.Count).Cells(1).CurrentRegion.Value
Me.ListBox1.List = sn
End If
Application.DisplayAlerts = False
Sheets(Sheets.Count).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
The SearchTextBox_AfterUpdate procedure creates a new worksheet after the existing worksheets. The new sheet is Sheets(Sheets.Count).
It calls ListFiles, which populates this sheet with a list of files.
The list box is populated from the used range of the new sheet.
Finally, that sheet is deleted again.
You could store the values in an array, but it would be more complicated because ListFiles calls itself recursively as it traverses the folder structure.
Thanks for the information. I'm getting error when I try to open the pdf from the listbox using the following code. What may I have been doing wrong in here?
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
ThisWorkbook.FollowHyperlink "K:\Downloads" & Me.ListBox1.Column(1) & ".pdf"
End Sub
1) You haven't added a backslash \ after the path K:\Downloads.
2) You haven't included the subfolder (if any).
3) You probably shouldn't add the extension.
Private Sub ListBox1_Click()
Dim sFile As String
sFile = "K:\Downloads\" & Me.ListBox1.Column(0) + "\" & Me.ListBox1.Column(1)
ThisWorkbook.FollowHyperlink sFile
End Sub
Private Sub ListBox1_Click()
Dim sFile As String
sFile = "K:\Downloads\" & Me.ListBox1.Column(0) + "\" & Me.ListBox1.Column(1)
Debug.Print sFile
End Sub
After double-clicking the list box and closing the userform, activate the Immediate window in the Visual Basic Editor (Ctrl+G).
Copy the path + filename that you see and paste it into a reply.
I don't get anything if I double click the listbox, close the form and open the immediate window.
However I get the following in the immediate window if I click a file from the listbox and then close it and open the immediate window. The folder name appears in double. Inventory is the name of the workbook.