Display a list of files based on 2 or 3 criteria search, the filles could be in different workbooks and subfolders

[Kurt]

I need to Display a list of files based on 2 or 3 criteria search, the filles could be in different workbooks and subfolders.

I know I will need to use file system objects, I would like to create a userform where a user can choose the files they want to copy to a new folder.

Has anyone done anything similar like this before?

 

[Leith Ross]

Hello Kurt,

What are the criteria you want to use?

 

[Kurt]

Hello Leith,

Thank you for replying. In the first search criteria will be a bank name such as Chase, Wells Fargo, etc.

The second criteria should be an amount say from 300 - 600 million or it could be 700 - 800 million.

I have learned in he past to allow for an extra criteria. I am just not sure what it would be yet.

I hope this helps answer your question.

There will be one main folder and the sub folders will have the bank names, so I don't think it will be several main folders.

I am still in discovery phase.

 

[Leith Ross]

Is the amount in the file or part of the file name?

 

[Kurt]

The amount is in the file name.

 

[davesexcel]

What do the filenames look like?

 

[Kurt]

BankNameA 300 - 600 Million BankNam3B 200 - 500 Million BankNameC 100-200 Million.

I am using generic names to protect the innocent! haha

 

[Leith Ross]

What is the file extension: xlsx, xlsm ?

 

[davesexcel]

Create a userform and add 2 comboboxes
add this code to the userform, change the initial folder location.

When you show the UserForm, combobox 1will list the subfolders
Once you have selected the subfolder combobox2 will list the files in the subfolder

Private Sub UserForm_Initialize()
    Dim fs, f, f1, fc, s
    Dim folderspec

    folderspec = "C:\TestFolder" & "\"

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.SubFolders

    ComboBox1.Clear

    For Each f1 In fc
        ComboBox1.AddItem f1.Name
    Next f1

    ComboBox1.SetFocus

End Sub
Private Sub ComboBox1_Change()

    Dim fs As Object, f As Object, f1 As Object, fc, s
    Dim folderspec

    folderspec = "C:\TestFolder" & "\" & ComboBox1

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.Files

    ComboBox2.Clear
    For Each f1 In fc
        ComboBox2.AddItem f1.Name
    Next f1

End Sub

 

[Kurt]

Thanks I will try this but I looked back at my notes.

Those key words can be in a document or a excel spreadsheet.

Is there a way to search inside the documents when the documents are closed?