HOW TO: Put Folder Contents into a Worksheet in Excel for Mac

This step-by-step article describes how to a create a Microsoft Visual Basic for Applications macro that prints the contents of a folder (directory) to a range of cells in a worksheet.

Macro Code Disclaimer

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

How to Create and Use the Macro

To programmatically print the contents of a folder to a range of cells in an Excel worksheet, follow these steps:

1. Start Excel.
2. On the Tools menu, point to Macro, and then click Visual Basic Editor.
3. On the Insert menu, click Module.
4. Type the following code in the module sheet:

   Sub Print_Dir_Contents()
   
      Dim Input_Dir, Print_File As String
   
      Input_Dir = InputBox("Type the path that contains the files you " & _
          "want to list in your worksheet." & Chr(13) & Chr(13) & _
          "For example, type <HD>:Documents:Microsoft User Data:") & Chr(13) & _
          "where <HD> is the name of your hard disk." & _
          & Chr(13) & Chr(13) & _
          "Be sure to include the colon (:) at the end of the path " & _
          "as in the example.")
      ' This code displays an input box in which you type the path
      ' to the folder for which you want to create a folder listing.
   
      If Input_Dir = "" Then Exit Sub
      ' If nothing is typed in the input box, the macro quits.
   
      Print_File = Dir(Input_Dir, MacID("TEXT"))
      ' For more information about MacID, please see Visual Basic Help.
   
      Range("a1").Select
      ' Select the first cell in the worksheet.
   
      Counter = 1
      ' Set the counter to 1.
   
      Do While Len(Print_File) > 0
          Worksheets(ActiveSheet.Name).Cells(Counter, 1).Value = _
              Print_File
          Print_File = Dir()
          Counter = Counter + 1
      Loop
      ' This routine inserts the found file names into the worksheet.
   
   End Sub
   					

5. On the File menu, click Close and Return to Microsoft Excel.
6. On the Tools menu, point to Macro, and then click Macros.
7. Under Macro name, click Print_Dir_Contents, and then click Run.

For additional information about macro programming resources, click the article number below to view the article in the Microsoft Knowledge Base: