File names to excel from respective folders

[harinsh]

Hi All,


I am working on some automation and need your help in vba code to get excel file names from respective folders......for example ..I have 10 different folders and need the file names those were inside the folder to excel cell starting from A1, A2, A3...so on...


if I get the complete path it would even really help me.....


Thanks in advance...

 

[MrIfOnly]

This code will put the file name in column A and the full path in column B. You'll have to put the folder paths in the folderlist array in the first sub.

Option Explicit

Dim fs, f1, f, fc
Dim n As String
Dim r As Integer, c As Integer
Dim folderspec As String


Sub folderloop()
Dim folderlist() As String
Dim x As Integer


Worksheets(1).Columns("A:B").ClearContents


folderlist = Split("C:\Users\username\folder1,C:\Users\username\folder2,C:\Users\username\folder3,C:\Users\username\folder4", ",")


r = 2


For x = 0 To 9
    folderspec = folderlist(x)
    listFiles
Next x


End Sub


Sub listFiles()
    With Worksheets(1)
        .Range("A1").Value = "File Name"
        .Range("B1").Value = "Full Path"
        .Range("A1:B1").Font.Bold = True
    End With
            
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.Files
    
    For Each f1 In fc
        If fs.GetExtensionName(f1) = "xls" Or fs.GetExtensionName(f1) = "xlsm" Then
            Sheets(1).Cells(r, 1) = fs.GetBaseName(f1)
            Sheets(1).Cells(r, 2) = f1
            r = r + 1
        End If
    Next
End Sub

Regards,

CJ