Sub ListAllFile() 
     
    Dim objFSO As Object 
    Dim objFolder As Object 
    Dim objFile As Object 
    Dim ws As Worksheet 
     
    Set objFSO = CreateObject("Scripting.FileSystemObject") 
    Set ws = Worksheets.Add 
     
     'Get the folder object associated with the directory
    Set objFolder = objFSO.GetFolder("C:\") 
    ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & "are:" 
     
     'Loop through the Files collection
    For Each objFile In objFolder.Files 
        ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name 
    Next 
     
     'Clean up!
    Set objFolder = Nothing 
    Set objFile = Nothing 
    Set objFSO = Nothing 
     
End Sub

http://www.ozgrid.com/forum/showthread.php?t=65530

Lists all filenames in a directory if you need them for formulae or add into VBA.

Excel (VBA) list all filenames in a directory

excel

Visual Basic