How to Have Excel Read a Directory Create a Spreadsheet

1. Click the Office button's 'Options' button, and then click the 'Show developer' checkbox to reveal the 'Developer' tab. This tab holds buttons for Visual Basic macros. You'll use a VBA macro to list the files in a directory and store that list in a new spreadsheet.
2. Click the 'Developer' tab, and then click the 'Visual Basic' button to enter the Visual Basic programming environment. Double-click the 'This workbook' item in the navigation pane at screen left. This action opens a new programming window in which to enter your directory listing program.
3. Paste the following program into the programming window. This program uses the VBA function 'Dir' to read a directory. The output of that function is a single file from that directory.Option ExplicitPublic Sub ListFiles()Dim dd = Dir('c:\windows\*')MsgBox dEnd Sub
4. Click any of the program's statements, and then click the 'Run' menu's 'Run' command to run the program. Excel will display a message box indicating the name of a file in the folder 'C:\windows.'
5. Paste the following program after the 'End sub' statement of the first program. This program creates a new workbook and inserts a value in one of its cells. Your final program that lists a directory's files will use the statements in this program to insert filenames into a new workbook.Option ExplicitPublic Sub MakeNewWorkbook()Workbooks.AddactiveCell = 'Hello there'End Sub
6. Run the program as you did the previous one, and then click the 'Excel' icon in the Windows taskbar to return to Excel. Your program will add a new workbook whose current spreadsheet displays the text 'Hello there' in cell A1.
7. Use step 2's instructions to return to the VBA programming environment, and then paste the following program after the 'End Sub' statement of the previous program. This program uses the 'Dir' function to gather all files in the 'C:\windows' folder. The program stores each filename in its own row in a spreadsheet of a new workbook.Option ExplicitPublic Sub ListOneFile()Dim dd = Dir('c:\windows\*')Workbooks.AddDo Until d = ''ActiveCell = d'move downActiveCell.Offset(1).Selectd = DirLoopEnd Sub
8. Run the program as you did the previous ones, and then return to Excel using step 6's instructions. You'll see a new spreadsheet whose cells display a listing of all files in the 'C:\windows' directory.