Getting A List Of File Names Using VBA

If your macro needs to present a list of files for the user to choose from, the
easiest approach is to use the GetOpenFileName method of the Application object.
For example, the code below displays the standard File Open dialog box. If the
user selects a file, the filename is stored in SelectedFile; if the user clicks
Cancel, SelectedFile is equal to False.

In some cases, however, you may want to get a list of all files in a
particular directory. The VBA function below (GetFileList) accepts a DOS path
and filespec as its argument, and returns a variant array that contains all of
the filenames in that directory. If no matching files are found, the function
returns False.

The subroutine listed below demonstrates how to use this function. In this
example, the filespec is passed to the GetFileList function and the result is
stored in x. If x is an array, it means that matching files were found. A
message box displays the number of files and the filenames are copied to column
A in Sheet1. If x is not an array, it means that no matching files were found.

Excel Tips

Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.

In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not
correspond to the Excel 2007 (and later) user interface.