Macro to Place Filenames in Given Directory on Worksheet

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
• Microsoft Excel for OS/2, versions 2.2, 3.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SUMMARY

The sample macros in this article place the names of all specified file types (specified by filename extension, for example .XLS) for a specified directory into a column on a worksheet.

MORE INFORMATION

Before you run these macros, you must have the destination sheet activated. The active cell in the worksheet should be the first cell of the range that the filenames are to be placed in.

To list different file types, you must modify the example macros by changing the argument in the Dir() function [FILES() function in the Microsoft Excel 4.0 example]. The Microsoft Excel 4.0 Macro Language example uses "C:\Excel\*.XLC." This returns all chart files located in the EXCEL directory. To return all Microsoft Excel add-in macros, replace "*.XLC" with "*.XLA," and so on. The specified directory can be any valid directory.

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

Visual Basic, Applications Edition, Example

1. In a new a module sheet, type the following:

         Sub ListFiles()
            F = Dir("C:\Excel\*.XLS")
            Do While Len(F) > 0
                  ActiveCell.Formula = F
                  ActiveCell.Offset(1, 0).Select
                  F = Dir()
             Loop
         End Sub
   
   

2. To run the macro, open a new worksheet. Select cell A1.

3. From the Tools menu, choose Macro. Select the ListFiles macro from the list of available macros, and choose Run.

To modify the macro so that it returns the filenames in a row instead of a column, change this line

   ActiveCell.Offset(1, 0).Select

   ActiveCell.Offset(0, 1).Select

Microsoft Excel 4.0 Macro Language

1. Enter the following information into a macro sheet:

         A1: ListFiles
         A2: =SET.NAME("FileArray",FILES("C:\Excel\*.XLC"))
         A3: =FOR("Counter",1,COLUMNS(FileArray),1)
         A4: =FORMULA(INDEX(FileArray,0,Counter))
         A5: =SELECT("R[1]C")
         A6: =NEXT()
         A7: =RETURN()
   

2. Define this macro by doing the following:

   a. Select cell A1.

   b. From the Formula menu, choose Define Name.

   c. Choose the Command button.

   d. Choose OK.

3. To run the macro, open a new worksheet. Select cell A1. From the Macro menu, choose Run. Select the ListFiles macro from the box and choose OK.

To modify the macro so that it returns the filenames in a row instead of a column, change cell A5 from R[1]C to RC[1].

REFERENCES

"Function Reference," version 4.0, pages 95, 147-148, 316, 378-381 "Function Reference," version 3.0, pages 46, 75, 177, 210 "Function and Macros," version 2.1, pages 266, 278,327, 348