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:

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

Test the code:

  1. With Excel open, open a New blank worksheet (if there's not one there already) by pressing Ctrl + N
  2. Click on any cell on the blank worksheet
  3. Press Alt + F8 to open the Macro dialogue box (or select Tools > Macro > Macros from the top Excel toolbar)
  4. 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')
  5. Click RUN (or double-click the macro name from the list)
  6. 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
  7. Click OK
  8. 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 923 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express