Data from different files

[sohaibj]

I've files of each employee (say 10 employees) in a folder say "ALL FILES".

I've save a file with name "XYZ" and want to save this file with the name of each file present in ALL FILES folder.

 

[Domenic]

The following macro will list in Column A of the active sheet the file names of each file within the folder 'ALL FILES'...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] ListFiles()

    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    [COLOR="Green"]'Change the path accordingly[/COLOR]
    strPath = "C:\ALL FILES\" 
    
    [color=darkblue]If[/color] Right(strPath, 1) <> "\" [color=darkblue]Then[/color] strPath = strPath & "\"
    
    strFile = Dir(strPath & "*.xls")
    
    [color=darkblue]Do[/color] [color=darkblue]While[/color] Len(strFile) > 0
        Cells(Rows.Count, "A").End(xlUp)(2).Value = strFile
        strFile = Dir
    [color=darkblue]Loop[/color]
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox "Completed...", vbInformation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]