Pages

Sunday, July 31, 2011

Print File Names in a folder and all sub folders to Excel on the basis of file type selected

If you want to get all file names from a folder and its sub folders to excel. Snapshot below-



Here is the code-

Dim j As Long
Dim fldpath
Dim fld, fil, subfld As Object

Sub list_of_file_names()
'********************* to get file names in folder
Application.FileDialog(msoFileDialogFolderPicker).Title = "Choose Folder"
Application.FileDialog(msoFileDialogFolderPicker).Show
fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Set fso = CreateObject("scripting.filesystemobject")
Set fld = fso.getfolder(fldpath)
getnames fld
End Sub

Sub getnames(ByRef prntfld)
For Each fil In prntfld.Files
j = Sheets(1).Range("A65356").End(xlUp).Row + 1
' WILL SEARCH FILES OF FILE TYPES SELECTED IN RANGE B1 OF SHEET 1
If UCase(Right(fil.Path, Len(Sheets(1).Range("b1").Value))) = UCase(Sheets(1).Range("b1").Value) Then
Cells(j, 1).Value = fil.Path
Cells(j, 2).Value = fil.Name
Cells(j, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Cells(j, 1).Value, TextToDisplay:=Cells(j, 1).Value
j = j + 1
End If
Next fil
For Each subfld In prntfld.SubFolders
getnames subfld
Next subfld

End Sub


Excel Macro File

http://www.filefactory.com/file/cdbb6b4/n/File_names_in_folder_and_sub_folder.xlsm

No comments:

Post a Comment