First of all Create a separate Macro Enabled Excel document (When you will save excel file then choose Save as type : Excel Macro-Enabled Workbook).
From Developer Ribbon Tab click on Visual Basic/ Go to View ribbon Tab and click on Macros >View Macros and Create new Macro with name say FilesInSideFolder.
In this Visual Basic editor we will be using Scripting File System Object so Go to Tools > References and Select ‘Microsoft Scripting Runtime’ reference and click ok on dialog box.
Now its time to Write our Macro.
First we will define File System Object in first line of code.
When you enter dot after scripting you will see list of options from that choose FileSystemObject
Copy and Paste Code below into your Visual Basic Editor :
‘This code is for Reference only by ITechOasis.com
Dim FSO As Scripting.FileSystemObject Dim sFolder As Scripting.Folder Dim sFile As Scripting.File Dim sPath As String Sub FilesInsideFolder() Set FSO = New Scripting.FileSystemObject sPath = "C:\temp" 'Give any Path name you want Set sFolder = FSO.GetFolder(sPath) 'Title Names Range("A1").Value = "Sr. No." Range("B1").Value = "File Name" Range("C1").Value = "File Path" Range("A1:C1").Font.Bold = True ' Starting Row r = 2 On Error Resume Next For Each sFile In sFolder.Files Cells(r, 1).Formula = r - 1 'Serial Number starts from row 2 Cells(r, 2).Formula = sFile.Name ' File Name with Extension Cells(r, 3).Formula = sFile.Path r = r + 1 Next sFile ' Coulmn Width Columns("A").ColumnWidth = 10 Columns("B").ColumnWidth = 30 Columns("C").ColumnWidth = 60 End Sub
You can try by changing sPath to any path you want and see if all File Names insider folder are listed in Excel Sheet!