Wednesday, 24 January 2018

Excel Macro to List Files in Folder


This post is to find how quickly we can list the files from folder in excel sheet. This is require if we want to know the file names and then manually searching them in folders is painful.

Open excel and using developer tool, copy the following code -

Option Explicit

Sub Macro1()

Dim fileRow As Long
Dim SelectFolder, GetFileName, StartingFolder
StartingFolder = "C:\"

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Select folder to list down files in Excel"
.InitialFileName = StartingFolder
If .SelectedItems.Count <> 0 Then
SelectFolder = .SelectedItems(1) & "\"
GetFileName = Dir(SelectFolder, 7)
Do While GetFileName  <> ""
ActiveCell.Offset(fileRow ) = GetFileName

fileRow = fileRow + 1
GetFileName  = Dir

End If
End With

End Sub

Save it and run it.. !

You will get the list of files instantly.  Simple.

Keep simplifying!!