Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Folder for Files
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Folder for Files
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Folder for Files
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 -- Hope that helps. Vergel Adriano "PaulW" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Folder for Files
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 :/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Folder for Files
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. -- Hope that helps. Vergel Adriano "PaulW" wrote: 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 :/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Folder for Files
Worked Brilliantly. Cheers.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling pdf files from general folder to specific folder | Excel Discussion (Misc queries) | |||
search folder/directory for a phrase inside excel files | Excel Discussion (Misc queries) | |||
User selection of folder and open all .xls files within folder | Excel Programming | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Programming |