How to Use Excel Filedialog to Get List of Files in a Directory

Written by brian valle
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Use Excel Filedialog to Get List of Files in a Directory
Create, edit, delete, monitor and move computer folders with Excel VBA. (Hemera Technologies/Photos.com/Getty Images)

Microsoft Excel is not just about tabular data, computations and information management. Any avid user can turn Excel into a powerful tool for managing folders, organising files and keeping track of tasks. You can create a macro that uses a FileDialog VBA object to quickly scan a directory and list its contents in a spreadsheet. Upon execution, this macro will prompt users to pick a folder, and it will subsequently list the names of its contents in a single column.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

    Start Microsoft Excel. Press "CTRL+O," select your workbook and then click on "Open." To create a new workbook instead, press "CTRL+N."

  2. 2

    Press "ALT+F8" to open the Macros dialogue box. Type "GetFolderContents" into the Macro name box and then click on "Create."

  3. 3

    Enter the following code into the VBA editor.

    Sub GetFolderContents()

    Dim xDir, xFilename, f, fso As Object

    Set fso = CreateObject("Scripting.FileSystemObject")

    With Application.FileDialog(msoFileDialogFolderPicker)

    .InitialFileName = ThisWorkbook.Path & "\"

    .Title = "Select a folder to list files from"

    .AllowMultiSelect = False

    .Show

    If .SelectedItems.Count <> 0 Then

      xDir = .SelectedItems(1) &amp; "\"
    
      End If
    

    End With

    If (MsgBox(Prompt:="Do you wish to include subfolder names?", _

    Buttons:=vbYesNo, Title:="Include Subfolders") = vbYes) Then

    GoTo ListFolders
    
    GoTo ListFiles
    
    Else
    
        GoTo ListFiles
    
    End If
    

    ListFolders:

    For Each f In fso.GetFolder(xDir).SubFolders
    
        ActiveCell.Value = "..\" &amp; f.Name
    
        ActiveCell.Offset(1, 0).Select
    
    Next f
    

    ListFiles:

    For Each f In fso.GetFolder(xDir).Files
    
        ActiveCell.Value = f.Name
    
        ActiveCell.Offset(1, 0).Select
    
    Next f
    

    Set fso = Nothing

    End Sub

  4. 4

    Press "ALT+Q" to close the VBA editor and return to your workbook.

  1. 1

    Highlight a cell in your sheet. Press "ALT+I" and then "C" to create a blank column. Select the first cell in the new column. If you'd like to display the list in a new worksheet instead, press "SHIFT+F11."

  2. 2

    Press "ALT+F8" to open the Macros dialogue box. Select "GetFolderContents" and then click on "Run."

  3. 3

    Select the folder or directory from which you'd like to list the contents. Click on "OK."

  4. 4

    Click on "Yes" if you'd like the macro to also list the names of sub-folders, or click on "No" to list files only. Wait for Excel to finish populating the new column with the contents of your selected folder.

Tips and warnings

  • By default, macros are disabled in Microsoft Excel. To enable them, click the Microsoft Office button and then go to "Excel Options." Ensure that "Show Developer tab" is checked. Go to "Trust Center" and then click "Trust Center Settings." Go to "Macro Settings" and then choose "Enable all macros." Click "OK" on both windows, save any open workbooks and then restart Excel.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.