Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Pulling Filenames into a Worksheet.
by Allen Wyatt
(last updated February 11, 2020)
Carol has a directory with about 1,000 files with names such as YR1905-LIC12345-Smith,Harry-Brown,Mary. She would like to bring all of these filenames (not the files themselves) into a worksheet and separate the names at the dash. Thus, the example filename would actually occupy four cells in a single row. Carol figures this will take a macro to accomplish, but she's not sure how to access the filenames in that macro.
You can, of course, use a macro to do this, but you don't need to use a macro. You can, instead, use an old DOS-era trick to get what you need. To access the command prompt if you are using a later version of Excel, simply type "command prompt" (without the quotes) in the search box next to the Windows icon in the task bar. If you are using an older version of Excel, the command prompt is accessible through Windows: Start | All Programs | Accessories | Command Prompt. At the command prompt navigate until you are in the directory that contains the files. Let's assume, for this example, that you are trying to get a listing of the files in this directory:
To navigate to that directory, enter in this command at the command prompt:
chdir "\Users\e07482\My Documents\rnp"
You need to use the quote marks around the directory because of the space in the path name. Then, type the following command to see, on-screen, what the file listing is:
dir /b /a-d
The "/a-d" part means "don't list directories." If you are satisfied with what you see on the screen, then you can send it to the file by using the following:
dir /b /a-d > filelist.txt
This creates a text file (filelist.txt) that contains a list of all the files within the current directory. Now, within Excel, you can follow these steps:
Figure 1. The Text Import Wizard.
The above steps are fairly easy to accomplish, particularly if you only need to get the file listing into Excel once in a while. If you need to do it more routinely, then you should probably seek a way to do it using a macro. The following macro will work very quickly:
Sub GetFileNames() Dim sPath As String Dim sFile As String Dim iRow As Integer Dim iCol As Integer Dim splitFile As Variant 'specify directory to use - must end in "\" sPath = "C:\" iRow = 0 sFile = Dir(sPath) Do While sFile <> "" iRow = iRow + 1 splitFile = Split(sFile, "-") For iCol = 0 To UBound(splitFile) Sheet1.Cells(iRow, iCol + 1) = splitFile(iCol) Next iCol sFile = Dir ' Get next filename Loop End Sub
When you run the macro, make sure that there is nothing in the current worksheet. (Anything there will be overwritten.) Also, you should change the directory path that is assigned to the sPath variable near the beginning of the macro.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11144) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Pulling Filenames into a Worksheet.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
You can use a macro to read information from a text file. The steps are easy, and then you can use that information in ...Discover More
When storing your Excel workbook, you need to specify a file name to be used for the workbook. Take a moment to consider ...Discover More
Open a workbook that someone else is working on, and you won't be able to save your changes back into the same file. ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.