Open folder with search value

User avatar
adam
SilverLounger
Posts: 2195
Joined: 23 Feb 2010, 12:07

Open folder with search value

Post by adam »

How can I change the location in this code to
\\myserver\my folder name?

Code: Select all

  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 
Any help would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Open folder with search value

Post by HansV »

Change

Code: Select all

searchlocation = "&crumb=location:C%3A%5CUsers%5CUSERNAME%5CDocuments"
to

Code: Select all

searchlocation = "&crumb=location:%5C%5Cserver%5Cmy%20folder%20name"
Character %5C = Chr(92) = "\" since hexadecimal 5C = decimal 92.
Character %20 = Chr(32) = " " since hexadecimal 20 = decimal 32
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2195
Joined: 23 Feb 2010, 12:07

Re: Open folder with search value

Post by adam »

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.

Code: Select all

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
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Open folder with search value

Post by HansV »

I have no idea how the two could be related.
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2195
Joined: 23 Feb 2010, 12:07

Re: Open folder with search value

Post by adam »

The following code does what I'm searching for. Instead of an input box I want to the search data to be written in a text box. How may I achieve this?

Code: Select all

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
Any help on this would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Open folder with search value

Post by HansV »

Change the line

Code: Select all

        FileFilter = InputBox("Enter the Full or Partial name of the Files to Find.")
to

Code: Select all

        FileFilter = UserForm1.SearchTextBox
where SearchTextBox is the name of the text box.
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2195
Joined: 23 Feb 2010, 12:07

Re: Open folder with search value

Post by adam »

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.

Code: Select all

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.

How could I get over this?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Open folder with search value

Post by HansV »

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.
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2195
Joined: 23 Feb 2010, 12:07

Re: Open folder with search value

Post by adam »

I've attached the file for your reference.

The user first loads the form.
Types the search value in the textbox.
and the search value gets displayed in the list box.

I hope I've made my question clear. Thanks in advance.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Open folder with search value

Post by HansV »

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.
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2195
Joined: 23 Feb 2010, 12:07

Re: Open folder with search value

Post by adam »

Thanks for the help Hans. The code works perfect.

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.

Code: Select all

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
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Open folder with search value

Post by HansV »

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.
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2195
Joined: 23 Feb 2010, 12:07

Re: Open folder with search value

Post by adam »

Thanks for the information. Cannot it be done without creating a sheet and then deleting the sheet?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Open folder with search value

Post by HansV »

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.
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2195
Joined: 23 Feb 2010, 12:07

Re: Open folder with search value

Post by adam »

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?

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
ThisWorkbook.FollowHyperlink "K:\Downloads" & Me.ListBox1.Column(1) & ".pdf"
End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Open folder with search value

Post by HansV »

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.

Code: Select all

Private Sub ListBox1_Click()
    Dim sFile As String
    sFile = "K:\Downloads\" & Me.ListBox1.Column(0) + "\" & Me.ListBox1.Column(1)
    ThisWorkbook.FollowHyperlink sFile
End Sub
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2195
Joined: 23 Feb 2010, 12:07

Re: Open folder with search value

Post by adam »

I'm getting the cannot open file error message with the above code?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Open folder with search value

Post by HansV »

Change the code to this:

Code: Select all

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.
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2195
Joined: 23 Feb 2010, 12:07

Re: Open folder with search value

Post by adam »

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.

I get K:\Downloads\Downloads\Inventory
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Open folder with search value

Post by HansV »

Thanks.

Do you really need the code to go into subfolders of K:\Downloads?
Regards,
Hans