Search Folder for Files

[PaulW]

I currently have a spreadsheet that I can use to search a folder using
Application.FileDialog(msoFileDialogFolderPicker) to give me a list of files,
then use formulas to find the files I want.

What I want now (for something else) is to search a Folder (preset based on
date) and return all the files as variables rather than as pasting them into
cells. There shouldn't be more than say 25 files in the folder, and no
subfolders. This will be used in a userform that will have 25 buttons that
allow the user to open any of the files easily. So I'll want the full path of
each file as a variable to assign to a command button (or have the macro
assign the filename when the file is found rather than to a tied in variable?)

I've looked at a couple of things and they seem vastly complex and I can't
seem to modify them to just give me the results as a variable rather than in
a cell.

 

[PaulW]

This is what i'm messing about with right now...


Sub foo2()

Dim f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14, f15,
f16, f17, f18, f19, f20, f21, f22, f23, f24, f25, f26, f27, f28, f29, f30 As
Variant

With Application.FileSearch
.NewSearch
.LookIn = "S:\Operations\Paul W\March"
.FileType = msoFileTypeExcelWorkbooks
.Execute

For i = 1 To .FoundFiles.Count
msgbox(.FoundFiles(i))
Next i

End With

End Sub

 

[Vergel Adriano]

Paul,

Give this a try:

Sub foo2()
Dim strPath As String
Dim colFiles As New Collection
Dim strFile As String
Dim i As Integer

strPath = "S:\Operations\Paul W\March\"
strFile = Dir(strPath)
Do While strFile <> ""
colFiles.Add strFile
strFile = Dir
Loop

For i = 1 To colFiles.Count
MsgBox strPath & colFiles(i)
Next i

End Sub

 

[PaulW]

That works great, cheers. Unfortunatly I've had trouble applying this to what
i'm doing

Private Sub Find_files()
Dim strPath As String
Dim colFiles As New Collection
Dim strFile As String
Dim i As Integer
Dim month As Variant
Dim year As Variant

year = ComboBox1.Value
month = ComboBox2.Value

strPath = "S:\MANAGEMENT INFORMATION\" & year & "\" & month & "\"
strFile = Dir(strPath)
Do While strFile <> ""
colFiles.Add strFile
strFile = Dir
Loop

CommandButton1.Caption = colFiles(1)

End Sub

with message boxes i've sorted the year/month thing so I know that works.
But the last line about changing the commandbutton caption throws up an error
when initialized (opening the userform triggers the macro after setting up
the combobox values)

I was assuming setting the command buttons to open the files to be harder
then just changing the captions :/

 

[Vergel Adriano]

Paul,

The code worked for me. But maybe try it this

If colFiles.Count > 0 Then
For i = 1 To colFiles.Count
Me.Controls("CommandButton" & i).Caption = colFiles(i)
Next i
End If

you need to make sure you have enough commandbuttons to hold each file name.

 

[PaulW]

Worked Brilliantly. Cheers.