Results 1 to 7 of 7

Thread: Gathering data from multiple workbooks.

  1. #1
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0

    Gathering data from multiple workbooks.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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

  3. #3
    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.
    Attached Files Attached Files
    Last edited by GaryA; 2012-05-04 at 11:11 PM.

  4. #4
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    Hi Simi,

    Try this:

    Code:
    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
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    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...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •