VBA Express : Excel - Get File Names from folder

Excel

Get File Names from folder

Ease of Use

Easy

Version tested with

2003, Vista

Submitted by:

Apps

Description:

An easy way to copy the names of all the contents of a selected folder onto an Excel spreadsheet

Discussion:

I am forever creating macros that rely on opening a list of selected files that were kept on a worksheet, and found it a pain to have to type the relative names onto a worksheet for the files I needed to reference, as I couldn't find an easy way to list a folder's contents from Excel or Windows Explorer. But using this code, I can very quickly navigate to the required target folder and all the filenames (of all filetypes) are copied onto my active worksheet in one list for me to then cut/copy/paste/reference as I need to. I first wrote this code with the use of an Input box for direction to the target folder, but after browsing some of John Walkenbach's excellent examples (j-walk.com) I changed it to utilise the FileDialog box to make it much more user friendly.

Code:

instructions for use

			Option Explicit 
 
Sub GetFileNames() 
     
    Dim xRow As Long 
    Dim xDirect$, xFname$, InitialFoldr$ 
     
    InitialFoldr$ = "G:\" '<<< Startup folder to begin searching from
     
    With Application.FileDialog(msoFileDialogFolderPicker) 
        .InitialFileName = Application.DefaultFilePath & "\" 
        .Title = "Please select a folder to list Files from" 
        .InitialFileName = InitialFoldr$ 
        .Show 
        If .SelectedItems.Count <> 0 Then 
            xDirect$ = .SelectedItems(1) & "\" 
            xFname$ = Dir(xDirect$, 7) 
            Do While xFname$ <> "" 
                ActiveCell.Offset(xRow) = xFname$ 
                xRow = xRow + 1 
                xFname$ = Dir 
            Loop 
        End If 
    End With 
End Sub

How to use:

This code is best placed in your Personal workbook, so it is always available for you to use
Copy the above code
In Excel press Alt + F11 to enter the Visual Basic Editor
Press Ctrl + R to show the Project Explorer
Right-click on the Personal file on left (in bold).
Choose Insert -> Module
Paste code into the right hand pane
Press Ctrl + S to Save the Personal file
Press Alt + Q to close the VB Editor

Test the code:

With Excel open, open a New blank worksheet (if there's not one there already) by pressing Ctrl + N
Click on any cell on the blank worksheet
Press Alt + F8 to open the Macro dialogue box (or select Tools > Macro > Macros from the top Excel toolbar)
Look for the macro named PERSONAL.XLS!GetFileNames and click on it (if you can't see it on the list, ensure that the bottom selection box states 'All Open Workbooks')
Click RUN (or double-click the macro name from the list)
The FileDialog box should now appear - navigate to the folder that you want to find the contents so the target Folder name is in the top 'Look in:' field
Click OK
The names of all of the contained Files (and their file type extensions e.g. '.xls', '.bmp')will be added to the worksheet as a vertical list, starting in the cell you selected

Sample File:

GetFileNamesEx.zip 10.27KB

Approved by mdmackillop

This entry has been viewed 1062 times.