Getting List of file in certain folder

[nareshjoshy]

Dera Sir,
I want to get all the name of files contain in certain folder in Excel by using VBA Code. I want to update all the files name in certain column in certain sheet. Can It possibe in excell.

Thank You in advance.

 

[DanteAmor]

Change text in red by your data

Sub getFiles()
    Dim folder As String, sfiles As Variant, i As Long
    folder = "[COLOR=#ff0000]C:\trabajo\files\[/COLOR]"
    If Right(folder, 1) <> "\" Then folder = folder & "\"
    
    wfiles = Dir(folder & "*.*")
    i = 1
    Do While wfiles <> ""
        Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Cells(i, "[COLOR=#ff0000]A[/COLOR]").Value = wfiles
        i = i + 1
        wfiles = Dir()
    Loop
End Sub

 

[nareshjoshy]

Thank you danteamor, It is working well.

Can we get

"C:\Users\DEO\Desktop\guarantee"

automatically where my file located.
I mean to say if My fie is located in "C:\Users\DEO\Desktop\guarantee" then it should be "C:\Users\DEO\Desktop\guarantee" but when I shift my file to folder
"C:\Users\DEO\Desktop\filefolder" it should be change to "C:\Users\DEO\Desktop\guarantee" automatic.

 

[DanteAmor]

Change:

folder = "C:\trabajo\files\"

By:

folder = thisworkbook.path & "\"

 

[nareshjoshy]

ilThank You sir, One More query to you.

Is there any option or code to execute this macro automatic when I open this file.

 

[DanteAmor]

In the events of thisworkbook put:

Private Sub Workbook_Open()
    Call getFiles
End Sub

Press Alt+F11 to VBA-Editor, press doubleclick on Thisworkbook, in the panel paste de code.

 

[nareshjoshy]

Thank You. It is working.

In This Code:

[COLOR=#333333]Sub getFiles()[/COLOR]    Dim folder As String, sfiles As Variant, i As Long
    folder = "[COLOR=#ff0000]C:\trabajo\files\[/COLOR]"
    If Right(folder, 1) <> "\" Then folder = folder & "\"
    
    wfiles = Dir(folder & "*.*")
    i = 1
    Do While wfiles <> ""
        Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Cells(i, "[COLOR=#ff0000]A[/COLOR]").Value = wfiles
        i = i + 1
        wfiles = Dir()
    Loop [COLOR=#333333]End Sub[/COLOR]

what should be added to clear previous data of updated column only and then add new data.

 

[DanteAmor]

Thank You. It is working.

In This Code:

what should be added to clear previous data of updated column only and then add new data.

Sub getFiles()    Dim folder As String, sfiles As Variant, i As Long
    folder = "C:\trabajo\files\"
    If Right(folder, 1) <> "\" Then folder = folder & "\"
    
    [COLOR=#0000ff]Sheets("Sheet1").columns("A").clearcontents[/COLOR]


    wfiles = Dir(folder & "*.*")
    i = 1
    Do While wfiles <> ""
        Sheets("Sheet1").Cells(i, "A").Value = wfiles
        i = i + 1
        wfiles = Dir()
    Loop 
End Sub

 

[nareshjoshy]

Thank you Dante Amor. Thank You very much.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.