Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can you pull filenames from a directory?
I have a directory with a bunch of Excel files. What I want to do is look in
that directory and put the names of the files in Column A. And can you limit it to only select the .xls files? Is this possible? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can you pull filenames from a directory?
This writes them to a new sheet but is easily convertable to write them to
sheet 1 id that's important to you. Alt+F11 insert module and paste this in When prompted enter an extension (XLS or DOC for example) navigate to the directory to search. Option Explicit Sub SrchForFiles() Dim i As Long, z As Long, Rw As Long Dim ws As Worksheet Dim y As Variant Dim fLdr As String, Fil As String, FPath As String y = Application.InputBox("Please Enter File Extension", "Info Request") If y = False And Not TypeName(y) = "String" Then Exit Sub Application.ScreenUpdating = False With Application.FileDialog(msoFileDialogFolderPicker) .Show fLdr = .SelectedItems(1) End With With Application.FileSearch .NewSearch .LookIn = fLdr .SearchSubFolders = True .Filename = y Set ws = ThisWorkbook.Worksheets.Add(Sheets(1)) On Error GoTo 1 2: ws.Name = "FileSearch Results" On Error GoTo 0 If .Execute() 0 Then For i = 1 To .FoundFiles.Count Fil = .FoundFiles(i) FPath = Left(Fil, Len(Fil) - Len(Split(Fil, "\")(UBound(Split(Fil, "\")))) - 1) If Left$(Fil, 1) = Left$(fLdr, 1) Then If CBool(Len(Dir(Fil))) Then z = z + 1 ws.Cells(z + 1, 1).Resize(, 4) = _ Array(Dir(Fil), _ FileLen(Fil) / 1000, _ FileDateTime(Fil), _ FPath) 'un REM the line below to have filenames as hyperlinks 'ws.Hyperlinks.Add Anchor:=Cells(z + 1, 1), _ Address:=.FoundFiles(i) End If End If Next i End If End With ActiveWindow.DisplayHeadings = False With ws Rw = .Cells.Rows.Count With .[A1:D1] .Value = [{"Full Name","Kilobytes","Last Modified", "Path"}] .Font.Underline = xlUnderlineStyleSingle .EntireColumn.AutoFit .HorizontalAlignment = xlCenter End With .[E1:IV1 ].EntireColumn.Hidden = True On Error Resume Next Range(Cells(Rw, "A").End(3)(2), Cells(Rw, "A")).EntireRow.Hidden = True Range(.[A2 ], Cells(Rw, "C")).Sort [A2 ], xlAscending, Header:=xlNo End With Application.ScreenUpdating = True Exit Sub 1: Application.DisplayAlerts = False Worksheets("FileSearch Results").Delete Application.DisplayAlerts = True GoTo 2 End Sub Mike "pokdbz" wrote: I have a directory with a bunch of Excel files. What I want to do is look in that directory and put the names of the files in Column A. And can you limit it to only select the .xls files? Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what do 'blue filenames' mean? | Excel Discussion (Misc queries) | |||
Using cell value in filenames | Excel Worksheet Functions | |||
retrieve filenames from given directory into excel | Excel Worksheet Functions | |||
prompting for filenames | Excel Discussion (Misc queries) | |||
Using Variables in filenames | Excel Discussion (Misc queries) |