Microsoft KB Archive/323164

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

Article ID: 323164

Article Last Modified on 2/1/2007

-

APPLIES TO


 * Microsoft Excel X for Mac
 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q323164



For a Microsoft Excel for Windows version of this article, see 213343.

IN THIS TASK
SUMMARY
 * Macro Code Disclaimer
 * How to Create and Use the Macro

REFERENCES



SUMMARY
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.

back to the top

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. back to the top

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:  Start Excel. On the Tools menu, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module.  Type the following code in the module sheet: Sub Print_Dir_Contents

Dim Input_Dir, Print_File As String

Input_Dir = InputBox(&quot;Type the path that contains the files you &quot; & _      &quot;want to list in your worksheet.&quot; & Chr(13) & Chr(13) & _       &quot;For example, type :Documents:Microsoft User Data:&quot;) & Chr(13) & _ &quot;where  is the name of your hard disk.&quot; & _ & Chr(13) & Chr(13) & _ &quot;Be sure to include the colon at the end of the path &quot; & _ &quot;as in the example.&quot;)  ' 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 = &quot;&quot; Then Exit Sub ' If nothing is typed in the input box, the macro quits.

Print_File = Dir(Input_Dir, MacID(&quot;TEXT&quot;)) ' For more information about MacID, please see Visual Basic Help.

Range(&quot;a1&quot;).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  On the File menu, click Close and Return to Microsoft Excel. On the Tools menu, point to Macro, and then click Macros.</li> Under Macro name, click Print_Dir_Contents, and then click Run.</li></ol>

back to the top

<div class="references_section">