Gathering data from multiple workbooks.

[Simi]

I am in need of collecting data from multiple workbooks, typically with just 1 sheet, and combining it on one form for easy viewing.
I am unsure of how to start this. The workbooks all have unique names with no pattern at all, but are all in the same folder.

For the sake of ease for an example:

Folder1 has 3 workbooks.
workbook1.xls
somebook.xls
dataofsomekind.xls

Each workbook has data in the same cells that I need to collect.
A1
A2
A10
A11

I would like the data put into a new workbook in the following way.
NewWorkbook.xls
A1 = workbook1
B1 = workbook1.cell(A1)
C1 = workbook1.cell(A2)
D1 = workbook1.cell(A10)
E1 = workbook1.cell(A11)

A2 = somebook
B2 = somebook.cell(A1)
C2 = somebook.cell(A2)
D2 = somebook.cell(A10)
E2 = somebook.cell(A11)

etc.

Thank you in advance for the help.
Simi

[Simi]

I have realized I need something more complex than I originally stated.

I will be having a folder with multiple folders inside of it that contain multiple workbooks. I.E.

MyDocuments has 5 folders in it.
Folder1
Folder2
Folder3
Folder4
Folder5

and there are multiple workbooks in each folder. I would need this code to be able to gather the data from all the workbooks contained in all the folders within MyDocuments.

I have been trying to browse the forums to see if this kind of thing has been asked, and found some good ideas, however it is above my programming understanding ;(

Thank you,

Simi

[GaryA]

Hi Simi,

See if the code in the attached file will help. Sorry, This was coded based on your first post.

Unzip and open MyWbk.xlsm.

You will need to edit the Test() routine for the Fpath to point to your folder.

[Simi]

Gary,

Thank you for the response.

I am trying to get away from needing to hard code the file names for my workbooks, as you do with your Wkbook array. This will need to be running on 1 folder, with 50+ sub folders, with 1-200 files per sub folder.

Simi

[Ken Puls]

Hi Simi,

Try this:

Sub TestMe()
    Dim sFolder As String

    Application.ScreenUpdating = False

    sFolder = "D:\My Documents\Test\"
    
    Call Consolidate(sFolder, ThisWorkbook)
End Sub


Private Sub Consolidate(strFolder As String, wbMaster As Workbook)
'Author          : Ken Puls (www.excelguru.ca)
'Function purpose: Consolidate data

    Dim wbTarget As Workbook
    Dim objFso As Object
    Dim objFiles As Object
    Dim objSubFolder As Object
    Dim objSubFolders As Object
    Dim objFile As Object
    Dim ary(3) As Variant
    Dim lRow As Long

    'Set Error Handling
    On Error GoTo EarlyExit

    'Create objects to enumerate files and folders 
    Set objFso = CreateObject("Scripting.FileSystemObject")
    Set objFiles = objFso.getfolder(strFolder).Files
    Set objSubFolders = objFso.getfolder(strFolder).subFolders

    'Loop through each file in the folder
    For Each objFile In objFiles
        If InStr(1, objFile.Path, ".xls") > 0 Then
            Set wbTarget = Workbooks.Open(objFile.Path)
            With wbTarget.Worksheets(1)
                ary(0) = .Range("A1")
                ary(1) = .Range("A2")
                ary(2) = .Range("A10")
                ary(3) = .Range("A11")
            End With
            
            With wbMaster.Worksheets(1)
                lRow = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Row
                .Range("A" & lRow & ":D" & lRow) = ary
            End With
            
            wbTarget.Close savechanges:=False
        End If
    Next objFile

    'Request count of files in subfolders
    For Each objSubFolder In objSubFolders
        Consolidate objSubFolder.Path, wbMaster
    Next objSubFolder

EarlyExit:
    'Clean up
    On Error Resume Next
    Set objFile = Nothing
    Set objFiles = Nothing
    Set objFso = Nothing
    On Error GoTo 0
End Sub

[Simi]

Ken,

Thank you very very much. I wish I understood how to make that kind of code. It seems to be working, now I am just going to have to add some options to it like browsing for a source folder. I have found some code to do that already

Simi

[Ken Puls]

Hi Simi,

The trick with this is that is uses the File Scripting object to look through all files in the folder you provide. It then calls itself to examine each subdirectory as well.

I actually have a GetOpenFileFrom routine, which I'm surprised that I have not yet posted on the site. I'll need to do that...