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:
Other People Are Reading
Start Microsoft Excel. Press "CTRL+O," select your workbook and then click on "Open." To create a new workbook instead, press "CTRL+N."
Press "ALT+F8" to open the Macros dialogue box. Type "GetFolderContents" into the Macro name box and then click on "Create."
Enter the following code into the VBA editor.
Dim xDir, xFilename, f, fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
.InitialFileName = ThisWorkbook.Path & "\"
.Title = "Select a folder to list files from"
.AllowMultiSelect = False
If .SelectedItems.Count <> 0 Then
xDir = .SelectedItems(1) & "\" End If
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
For Each f In fso.GetFolder(xDir).SubFolders ActiveCell.Value = "..\" & f.Name ActiveCell.Offset(1, 0).Select Next f
For Each f In fso.GetFolder(xDir).Files ActiveCell.Value = f.Name ActiveCell.Offset(1, 0).Select Next f
Set fso = Nothing
Press "ALT+Q" to close the VBA editor and return to your workbook.
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."
Press "ALT+F8" to open the Macros dialogue box. Select "GetFolderContents" and then click on "Run."
Select the folder or directory from which you'd like to list the contents. Click on "OK."
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.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for