Copy data from multiple folders

[chatkinson3001]

Hi People,

Was wondering if you could help. I am a bit of a macro novice and have been struggling with this for a while.

I am trying to copy 1 cell from multiple worksheets (that are all named the same) that are stored in different folders into one worksheet. The multiple folders are all of the same level and were generated each time a test was run. They all contain exactly the same files (ie. same file names) but the files contain different data.

Ideally I would then like to average the data.

Regards,
chatkinson3001

 

[Trevor G]

Welcome to the Forum,

Which version of MS Office are you using?

 

[hippiehacker]

to loop through folders and subfolders you can use

'Requires reference to Microsoft Scripting Runtime

Option Explicit

Public Sub Process_Orders()
    
    Dim Fso As Scripting.FileSystemObject
    
    Set Fso = New Scripting.FileSystemObject
    Process_XLS_Files Fso, "C:\"
    
    Set Fso = Nothing

End Sub


Private Sub Process_XLS_Files(Fso As Scripting.FileSystemObject, folderPath As String)
   
    Dim Folder As Scripting.Folder, Subfolder As Scripting.Folder, File As Scripting.File
    Dim wb As Workbook
    
    Set Folder = Fso.GetFolder(folderPath)
    
    For Each Subfolder In Folder.subfolders
        For Each File In Subfolder.Files
            If InStr(File.Name, ".xls") Then
                Debug.Print File.Path
                'Open File.Path workbook here and call your code on it
            End If
        Next
    Next

End Sub

 

[chatkinson3001]

Cheers guys, I'll try to get this working. I'll let you know if I have any problems.

I'm using MS 2010 by the way.

Apologies for the late reply, have been away from my computer for a while.