Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Nov 2013
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Merge multiple workbook with 1 sheet each from one data to another

    Hi,

    Can someone help me to generate a code that will consolidate/merge data from 29 workbooks with only one sheet per workbook. I have a header that says Group NAME, ADDRESS, CODE, TYPE, COLOR, TIME on my consolidatator. However data coming from 29 workbooks dont have a standard header. Workbook 1 has only GroupNAME, CODE, TYPE, while Workbook 2 has GroupNAME ADDRESS, COLOR.. Workbook 3 has GroupNAME, ADDRESS, COLOR, TIME as their header. I need to merge all data from all those workbook to 1 master consolidator by using a GetOPenFile function so I can select multiple files and it will automatically consolidate all in one file. Hope you can assist me. Thank you

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    kramoir,

    Would these headers always be in the same row(s)? If so, the job just got a whole lot easier.

    Maud

  3. #3
    New Lounger
    Join Date
    Nov 2013
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Maud,

    The main or master file has a fixed header like: GROUP NAME, ADDRESS, CODE, TYPE, COLOR, TIME. the only problem im facing was on the other workbook is not standard as the master header. Workbook 1 has only GroupNAME, CODE, TYPE, while Workbook 2 has GroupNAME ADDRESS, COLOR.. Workbook 3 has GroupNAME, ADDRESS, COLOR, TIME. My problem is I dont know how to align the data according to the column in the master file since other columns in 3 workbook mentioned is not fixed: please see sample data below




    WORKBOOK 1:
    GROUPNAME CODE TYPE
    group 1 2 ax
    group 1 3 ay
    group 1 aa


    WORKBOOK2:
    GROUPNAME ADDRESS COLOR
    group 2 Canada Blue
    group 2 Sommerville Green

    WORKBOOK 3:
    GROUPNAME ADDRESS COLOR TIME
    group 3 China Red afternoon
    group 3 Singapore Blue morning
    group 3 Vietnam White evening


    MASTERFILE:
    GROUPNAME ADDRESS CODE TYPE COLOR TIME
    group 1 2 ax
    group 1 3 ay
    group 1 aa
    group 2 Canada Blue
    group 2 Sommerville Green
    group 3 China Red morning
    group 3 Singapore Blue afternoon
    group 3 Vietnam White evening


    This is the outcome that I want to appear in the masterfile: using GetOpenFile function so I can just select multiple files and consolidate everything. Hope you can provide me an idea. I can tweak it myself, I just need help on how to place it the way it should be placed like the master file looks. thank you

  4. #4
    New Lounger
    Join Date
    Nov 2013
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kramoir View Post
    Hi Maud,

    The main or master file has a fixed header like: GROUP NAME, ADDRESS, CODE, TYPE, COLOR, TIME. the only problem im facing was on the other workbook is not standard as the master header. Workbook 1 has only GroupNAME, CODE, TYPE, while Workbook 2 has GroupNAME ADDRESS, COLOR.. Workbook 3 has GroupNAME, ADDRESS, COLOR, TIME. My problem is I dont know how to align the data according to the column in the master file since other columns in 3 workbook mentioned is not fixed: please see sample data below




    WORKBOOK 1:
    GROUPNAME CODE TYPE
    group 1 2 ax
    group 1 3 ay
    group 1 aa


    WORKBOOK2:
    GROUPNAME ADDRESS COLOR
    group 2 Canada Blue
    group 2 Sommerville Green

    WORKBOOK 3:
    GROUPNAME ADDRESS COLOR TIME
    group 3 China Red afternoon
    group 3 Singapore Blue morning
    group 3 Vietnam White evening


    MASTERFILE:
    GROUPNAME ADDRESS CODE TYPE COLOR TIME
    group 1 2 ax
    group 1 3 ay
    group 1 aa
    group 2 Canada Blue
    group 2 Sommerville Green
    group 3 China Red morning
    group 3 Singapore Blue afternoon
    group 3 Vietnam White evening


    This is the outcome that I want to appear in the masterfile: using GetOpenFile function so I can just select multiple files and consolidate everything. Hope you can provide me an idea. I can tweak it myself, I just need help on how to place it the way it should be placed like the master file looks. thank you
    THE MASTER FILE SHOULD ALIGN THE DETAILS STATED IN EACH COLUMNS: thank you

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Kramoir,

    That part is quite clear, however, my question is, "Will the header row (no matter what the name and number of the columns are) always be in row 1 in all the workbooks? In other words, will one book have the header in row 3 while another in row 2 and maybe yet another will have the header row in row 1?

    If the headers for all the book are in row 1, that eliminates the need for a search and the code becomes quite simple.

    Maud

  6. #6
    New Lounger
    Join Date
    Nov 2013
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Maud,

    All Headers starts in column A1 in all workbooks.


    Thank you,
    Kramoir

  7. #7
    New Lounger
    Join Date
    Nov 2013
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Any idea on this? Appreciate your help. Thanks

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    K, I'm on it!

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Kramoir,

    This code will open the file you specify and copy the data to the Master File and place in the appropriate columns. The image below shows 3 files that were imported. You did not specify if the files were in the same or in various folders so I coded it for you to browse to each file and select it for import. If they are all in the same folder then let me know.

    HTH,
    Maud

    MasterFile1.png

    Code:
    Sub OpenFile()
    'DECLARE AND SET VARIABLES
    Dim fileName
    Dim Header(6)
    Application.ScreenUpdating = False
    '---------------------------------------------
    'OPEN SELECTED FILE AND GET FILE NAME
    fileName = Application.GetOpenFilename("All Excel Files(*.xl*),*.xl*")
    If fileName <> "False" Then
        s = Split(fileName, "\")
        wbName = s(UBound(s))
        Workbooks.Open fileName 'OPEN SOURCE FILE
    '---------------------------------------------
    'SET VARIABLES
        With Workbooks("MasterFile_Rev1.xlsm").Worksheets("Master")
        LastCol = Cells(1, Application.Columns.Count).End(xlToLeft).Column
        LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        tRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    '---------------------------------------------
    'GET HEADER ROW INTO ARRAY VARIABLE
        For I = 1 To LastCol
            Header(I) = Cells(1, I)
        Next I
    '---------------------------------------------
    'GET AND SEND DATA
        For H = 2 To LastRow 'CYCLE THROUGH ROWS IN SOURCE FILE
            For I = 1 To LastCol 'CYCLE THROUGH COLUMNS IN SOURCE FILE
                Select Case Header(I)
                    Case "GroupName"
                        .Cells(tRow, 1) = Cells(H, I)
                    Case "Address"
                        .Cells(tRow, 2) = Cells(H, I)
                    Case "Code"
                        .Cells(tRow, 3) = Cells(H, I)
                    Case "Type"
                        .Cells(tRow, 4) = Cells(H, I)
                    Case "Color"
                        .Cells(tRow, 5) = Cells(H, I)
                    Case "Time"
                        .Cells(tRow, 6) = Cells(H, I)
                End Select
            Next I
            tRow = tRow + 1
        Next H
    End With
    End If
    ActiveWorkbook.Close
    Cells.Columns.AutoFit
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  10. #10
    New Lounger
    Join Date
    Nov 2013
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Thank you very much you are such a genius. This is what I really wanted to accomplish.

    I more favor. All files are located in one folder how can i get all the files and paste in the master file?

    and with regard to the code below. is it possible to just use any name of file coz below code only reads if the workbook is named MASTERFILE_REV1.XLSM..

    With Workbooks("MasterFile_Rev1.xlsm").Worksheets("Mast er")


    Thanks much,
    marquee
    Last edited by kramoir; 2013-11-19 at 05:55.

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    kramoir,

    I will start to work on uploading the files from the folder assuming that all .xls, xlsx, .xlsm, or .xlsb files located in the folder are the ones you want to upload into the master file. The code can be adjusted to any name the master file any name you wish. In the code, you can change it your self or let me know and I will change it for you. You will also need to let me know what the name of the worksheet is as well (Currently "Master").

    Maud

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Kramoir,

    Here is the adjusted code that will allow you to select the folder that holds all the files you wish to consolidate. You will first be presented with the "Browse Folder" dialogue box (see image). After selecting you folder and clicking OK, it will open the first .xl* file, load each row into the appropriate columns, close, then repeat until all the files in that immediate folder have been copied. No intervention needed.

    Hope this meets your needs

    MasterFile2.png MasterFile1.png

    Code:
    Sub OpenFile()
    Application.ScreenUpdating = False
    '---------------------------------------------
    'DECLARE AND SET VARIABLES
    Dim wbk As Workbook
    Dim Filename As String
    Dim Path As String
    Dim ShellApp As Object
    Dim Header(6)
    '---------------------------------------------
    'SELECT FOLDER WITH EXCEL FILES
    Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
    Path = ShellApp.self.Path & "\"
    Filename = Dir(Path & "*.xl*")
    '--------------------------------------------
    'OPEN EXCEL FILES
    Do While Len(Filename) > 0  'IF LAST WORKBOOK HAS NOT YET BEEN OPENED
        Set wbk = Workbooks.Open(Path & Filename)
    '---------------------------------------------
    'SET ADDITIONAL VARIABLES
        With Workbooks("MasterFile_Rev2.xlsm").Worksheets("Master")
        LastCol = Cells(1, Application.Columns.Count).End(xlToLeft).Column '# OF COLS IN SOURCE
        LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row  '# OF ROWS IN SOURCE
        tRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1  'STARTING ROW IN MASTER
    '---------------------------------------------
    'GET HEADER ROW INTO ARRAY VARIABLE
        For I = 1 To LastCol
            Header(I) = Cells(1, I)
        Next I
    '---------------------------------------------
    'GET AND SEND DATA
        For H = 2 To LastRow 'CYCLE THROUGH ROWS IN SOURCE FILE
            For I = 1 To LastCol 'CYCLE THROUGH COLUMNS IN SOURCE FILE
                Select Case Header(I)
                    Case "GroupName"
                        .Cells(tRow, 1) = Cells(H, I)
                    Case "Address"
                        .Cells(tRow, 2) = Cells(H, I)
                    Case "Code"
                        .Cells(tRow, 3) = Cells(H, I)
                    Case "Type"
                        .Cells(tRow, 4) = Cells(H, I)
                    Case "Color"
                        .Cells(tRow, 5) = Cells(H, I)
                    Case "Time"
                        .Cells(tRow, 6) = Cells(H, I)
                End Select
            Next I
            tRow = tRow + 1  'ADVANCE TO NEXT ROW ON MASTER
        Next H
        End With
        wbk.Close True
        Filename = Dir  'SET TO NEXT FILE
        Cells.Columns.AutoFit
    Loop
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    kramoir (2013-11-20)

  14. #13
    New Lounger
    Join Date
    Nov 2013
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Thank you very much. I cannot thank you enough. Thank you thank you for your help, I dont know what to do without you. thanks again

    Kramoir

Tags for this Thread

Posting Permissions

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