VB code to list all files in a folder

[pagrender]

Hello,

Is there a way to have Excel list all the names of all the files that are in one folder?

For example, in the "Budgets" folder found using Windows Explorer, there are two files named 2011-12 and 2012-13.

Is there any VB code that will list these two files in cells A1 and A2 respectively? All we are looking for is the file name in a cell.

Thanks,
Pete

 

[VoG]

Hi. Try like this

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    j = j + 1
    Cells(j, 1).Value = MyFile
    MyFile = Dir
Loop
End Sub

 

[Ruddles]

A very simple version would be:-

Option Explicit
 
Public Sub ListFiles()
 
  Dim sFilename As String
  Dim iRow As Integer
  
  sFilename = Dir("[COLOR=red][B]c:\temp\[/B][/COLOR]")
  iRow = 0
  Do Until sFilename = ""
    iRow = iRow + 1
    Cells(iRow, 1) = sFilename
    sFilename = Dir
  Loop
    
End Sub

You would put your folder location in place of the red bit.

Or are you wanting to navigate to the folder as part of the code?

 

[pagrender]

That worked! Thanks a bunch!