Pages

Sunday, July 31, 2011

Print File Names in a folder to Excel on the basis of file type selected

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




Here is the code-

Sub list_of_file_names()
'********************* to get file names in folder
Dim j As Long
Dim fldpath
Dim fld, fil As Object


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)
For Each fil In fld.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
End Sub

Excel Macro File http://www.filefactory.com/file/cda6cc7/n/FILE_NAMES_OF_SPECIFIC_TYpe.xlsm

No comments:

Post a Comment