Copy Files Names in Eplorer using VBA Macro in Excel

**gjohn282** · 05-09-2011, 07:04 PM

I need some help writting a marco that will copy all file names inside a designated folder (I.e. windows explorer on my c: drive) and then paste the results into my open spread sheet. Ideally, I would like to have my spread sheet open, then run the macro, and then the file names will appear. One file name per cell. Thank you for any help!!

**Richard Buttrey** · 05-09-2011, 08:11 PM

Hi,

One way:

Sub ListFileNames()
    Dim fso, ofileItem, oSource, stDirectory As String, lCounter As Long
    
    stDirectory = "C:\" 'your folder - change as appropriate
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oSource = fso.GetFolder(stDirectory)

    For Each ofileItem In oSource.Files
        lCounter = lCounter + 1
        Sheets("sheet1").Cells(lCounter, 1) = ofileItem.Name
    Next ofileItem

    Set fso = Nothing
End Sub

Regards