Wednesday 24 January 2018

Excel Macro to List Files in Folder

Hello,

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
.Show
If .SelectedItems.Count <> 0 Then
SelectFolder = .SelectedItems(1) & "\"
GetFileName = Dir(SelectFolder, 7)
Do While GetFileName  <> ""
ActiveCell.Offset(fileRow ) = GetFileName

fileRow = fileRow + 1
GetFileName  = Dir

Loop
End If
End With



End Sub






Save it and run it.. !

You will get the list of files instantly.  Simple.

Keep simplifying!!