Saturday, May 26, 2012

Get Names of all the files in a folder Including Sub folder

If you want to get the names of all the files stored in a folder and including the files stored in a sub folder.Try below code-

Sub file_names_including_sub_folder()
Application.ScreenUpdating = False
Dim fldpath
Dim fld As Object, fil As Object, fso As Object, j As Long
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Choose the folder"
End With
On Error Resume Next
fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
If fldpath = False Then
MsgBox "Folder Not Selected"
Exit Sub
End If
Cells(1, 1).Value = fldpath
Cells(2, 1).Value = "Path"
Cells(2, 2).Value = "Dir"
Cells(2, 3).Value = "Name"
Cells(2, 4).Value = "Size"
Cells(2, 5).Value = "Type"
Cells(2, 6).Value = "Date Created"
Cells(2, 7).Value = "Date Last Access"
Cells(2, 8).Value = "Date Last Modified"
Set fso = CreateObject("scripting.filesystemobject")
Set fld = fso.getfolder(fldpath)
j = 4
get_sub_foldernames fld
Range("a1").Font.Size = 9
ActiveWindow.DisplayGridlines = False
Range("a3:h" & Range("a4").End(xlDown).Row).Font.Size = 9
Range("a2:h2").Interior.Color = vbCyan
Application.ScreenUpdating = True
End Sub
Sub get_sub_foldernames(ByRef prntfld As Object)
Dim subfld As Object, fil As Object, j As Long
For Each fil In prntfld.Files
j = Range("A1").End(xlDown).Row + 1
Cells(j, 1).Value = fil.Path
Cells(j, 2).Value = Left(fil.Path, InStrRev(fil.Path, "\"))
Cells(j, 3).Value = fil.Name
Cells(j, 4).Value = fil.Size
Cells(j, 5).Value = fil.Type
Cells(j, 6).Value = fil.DateCreated
Cells(j, 7).Value = fil.DateLastAccessed
Cells(j, 8).Value = fil.DateLastModified
For Each subfld In prntfld.SubFolders
get_sub_foldernames subfld
Next subfld
End Sub

No comments:

Post a Comment

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...