How to get all files name from a folder in Excel (VBA).

In this post I will show, how to get all files name from a folder. Some times we require to type all file names from a folder. Typing for many files a time consume. So to get easily all files name we may use following procedure. Sample File Download Link.

1.       Open a new excel file.

2.       Open Visual Basic code windows (Hit “ALT+F11”).

3.       Insert a new module.

4.       Copy and paste below codes and save the file as macro enable workbook.

‘******************************************************************************

Sub GetAllFiles()

    Dim fso As Object

    Dim objFolder As Object

    Dim objFolders As Object

    Dim objFile As Object

    Dim objFiles As Object

    Dim strFileName As String

    Dim strFilePath As String

    Dim FolderPath, FolderNme As String

   

        With Application.FileDialog(msoFileDialogFolderPicker)

            .Show

            FolderNme = .SelectedItems(1)

        End With

    FolderPath = FolderNme

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set objFolder = fso.GetFolder(FolderPath)

    Set objFiles = objFolder.Files

   

    For Each objFile In objFiles

        strFileName = objFile.Name

          strFilePath = objFile.Path

           ActiveCell.Value = strFileName

          ActiveCell.Offset(0, 1) = strFilePath

        ActiveCell.Offset(1, 0).Activate

    Next

    Set objFolder = Nothing

    Set objFile = Nothing

    Set fso = Nothing

    'Columns(ActiveCell.Column).AutoFit

    'Columns(ActiveCell.Offset(0, 1).Column).AutoFit

End Sub

‘******************************************************************************

5.       Now insert a form controls button from developer tab and assign above macro.

6.       Select cell A1 and run the macro by clicking the button.

7.       Macro will get all files from the folder you will select.

If there is any query about this post, you can comment or send mail to harun24hr@gmail.com