Posted By

cueballrawn on 07/22/16


Tagged

excel vba filenames


Versions (?)

Excel (VBA) list all filenames in a directory


 / Published in: Visual Basic
 

URL: 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.

  1. Sub ListAllFile()
  2.  
  3. Dim objFSO As Object
  4. Dim objFolder As Object
  5. Dim objFile As Object
  6. Dim ws As Worksheet
  7.  
  8. Set objFSO = CreateObject("Scripting.FileSystemObject")
  9. Set ws = Worksheets.Add
  10.  
  11. 'Get the folder object associated with the directory
  12. Set objFolder = objFSO.GetFolder("C:\")
  13. ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & "are:"
  14.  
  15. 'Loop through the Files collection
  16. For Each objFile In objFolder.Files
  17. ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name
  18. Next
  19.  
  20. 'Clean up!
  21. Set objFolder = Nothing
  22. Set objFile = Nothing
  23. Set objFSO = Nothing
  24.  
  25. End Sub

Report this snippet  

You need to login to post a comment.