Macro to Place Filenames in Given Directory on Worksheet

ID: Q74493

The information in this article applies to:

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

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

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

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

All workbook files located in your EXCEL directory will be listed in column A in the worksheet.

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

to this:


   ActiveCell.Offset(0, 1).Select

Microsoft Excel 4.0 Macro Language

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()

Define this macro by doing the following:
1. Select cell A1.
2. From the Formula menu, choose Define Name.
3. Choose the Command button.
4. Choose OK.

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.

All chart files located in your EXCEL directory will be listed in column A in the new worksheet.

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

Additional query words: directories folder

Keywords : kbcode kbmacro
Version : WINDOWS:2.x,3.0,4.0,5.0,7.0,97; :2.2,3.0
Platform : WINDOWS
Issue type :