Macro to compile data from multiple excel files into one summary file

[Kiwirunner]

Hi,

I have one hundred excel files that have the same identical format but have different guest names, guest addresses and arrival dates. What I am trying to do is write a macro that goes into each file, looks to see how many guest names are in each file and then copies this information along with the guests address information into a summary excel sheet.

The 100 identical excel files look as follows (each file will be saved as the group name i.e. "Group 12"):

A B C D E
1 Group 12
2
3 Guest Name Address Arrival Date
4 Joe Bloggs Australia 21/1/12
5 James Henry UK 22/1/12
6 Sarah Henry UK 22/1/12

I am trying to get the summary file to look as follows:

A B C D E
1 Summary File
2
3 Group Guest Name Address Arrival Date
4 12 Joe Bloggs Australia 21/1/12
5 12 James Henry UK 22/1/12
6 12 Sarah Henry UK 22/1/12
7 13 Andrew Walker UK 28/2/12
8 13 Kate Henly USA 29/2/12
9 14 Andy Eaden A 29/2/12

Any help in pointing me in the right direction here would be most appreciated.

Thanks and have a happy NYE!

 

[Ashwin_max]

AWESOME!!
Thanks much Jerry!!!!
Code provided by you works like charm and does the work. Comments provided in your code are very detailed and really allows one to modify code as per needs. Must say, u hv got awesome programming skills!!
Just found something in your code which I wanted to edit but was not able to, may you can help me here. After you append data to the master report, your code would move source files to 'Imported' folder and if ever the files already exists in Imported folder, your code fails saying 'Files already exists'. I would like to know waht changes will I have to make to the code, so that it would overwrite the files, if they exists in Imported folder??
Thanks in advance!!

 

[jbeaucaire]

Try adding:

Application.DisplayAlerts = False

...near the top with the Dims. This code makes alert messages NOT appear and Excel applies the default answers, typically "yes". Give that a shot.

 

[el_garicimo]

Hi Jerry,

I recently came across this thread and yours and was delighted to find that it treated my issue exactly--also that you had a wonderful website at https://sites.google.com/a/madrocke.../merge-functions/consolidate-wbs-to-one-sheet with many useful and educational bits of code.

I tried using your code, however, and am coming across a bug I can't fix. I am very new to VBA programming, hopefully you can help me out.

I get a 424 "object required" error in the following line of code: Range("A1:A" & LR).EntireRow.Copy.Range ("A" & NR)

For context, the line is highlighted in red in the full macro I'm using, below:

Option Explicit

Sub Consolidate()
'Author:     Jerry Beaucaire'
'Date:       9/15/2009     (2007 compatible)  (updated 4/29/2011)
'Summary:    Merge files in a specific folder into one master sheet (stacked)
'            Moves imported files into another folder

Dim fName As String, fPath As String, fPathDone As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wsMaster As Worksheet

'Setup
    Application.ScreenUpdating = False  'speed up macro execution
    Application.EnableEvents = False    'turn off other macros for now
    Application.DisplayAlerts = False   'turn off system messages for now
    
    Set wsMaster = ActiveWorkbook.Sheets("Master")    'sheet report is built into

With wsMaster
    If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
        .UsedRange.Offset(1).EntireRow.Clear
        NR = 2
    Else
        NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1    'appends data to existing data
    End If

'Path and filename (edit this section to suit)
    MsgBox "Please select a folder with files to consolidate" 'Creates message box prompting
    Do                                                        'user to chose folder containing
        With Application.FileDialog(msoFileDialogFolderPicker) 'workbooks to be compiled
            .InitialFileName = "C:\2010\Test\"
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.Count > 0 Then
                fPath = .SelectedItems(1) & "\"
                Exit Do
            Else
                If MsgBox("No folder chose, do you wish to abort?", _
                    vbYesNo) = vbYes Then Exit Sub
            End If
        End With
    Loop
    fPathDone = fPath & "Imported\"     'remember final \ in this string
    On Error Resume Next
        MkDir fPathDone                 'creates the completed folder if missing
    On Error GoTo 0
    fName = Dir(fPath & "*.xls")        'listing of desired files, edit filter as desired

'Import a sheet from found files
    Do While Len(fName) > 0
        If fName <> ThisWorkbook.Name Then              'don't reopen this file accidentally
            Set wbData = Workbooks.Open(fPath & fName)  'Open file

        'This is the section to customize, replace with your own action code as needed
            LR = Range("A" & Rows.Count).End(xlUp).Row  'Find last row
            Range("A1:A" & LR).EntireRow.Copy.Range ("A" & NR)
            wbData.Close False                                'close file
            NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1  'Next row
            Name fPath & fName As fPathDone & fName           'move file to IMPORTED folder
        End If
        fName = Dir                                       'ready next filename
    Loop
End With

ErrorExit:    'Cleanup
    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True         'turn system alerts back on
    Application.EnableEvents = True          'turn other macros back on
    Application.ScreenUpdating = True        'refreshes the screen
End Sub

I am using a test work sheet where cells A1:M15 are filled with text, and I am not clearing the data first. "master sheet" I am working in is in the same folder as the other files I want to compile (three other test files). The three other test files are not macro enabled workbooks. I am using Excel 2010. The macro gives the same error when it is a module both in my personal macro document, and a module in the "master workbook" itself.

Maybe one of the details in the paragraph above will make it clear why your code seems to be working for everyone else but not for me :/

Will definitely "contribute" towards that steak dinner if you can help out!

Thanks,
Garrett

 

[aditi_lic]

Download and use this excel macro file to automated merge multiple files sheets into one master. Works with 2010, 07 and 03.
Merge Excel Macro File

 

[Kazdima]

Does it consolidate data from many files into one report?

 

[Dedonovan]

I had this same question last week and have had luck with this code: This will past the worksheet in Outlook as an object.

Sub Mail_Sheet_Outlook_Body()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Don't forget to copy the function RangetoHTML in the module.
'Working in Excel 2000-2013
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With


    Set rng = Nothing
    Set rng = ActiveSheet.UsedRange
    'You can also use a sheet name
    'Set rng = Sheets("YourSheet").UsedRange


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
    With OutMail
        .To = "XXX@ZZZZ.com"'Change this line
        '.CC = ""
        '.BCC = ""
        .Subject = "Test 123"
        .HTMLBody = "All," & "<br>" & "Please see the _____ below:" & RangetoHTML(rng)
        '.Send   'or use .Display
        .Display
    End With
    On Error GoTo 0


    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub




Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2013
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook


    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"


    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With


    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With


    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")


    'Close TempWB
    TempWB.Close savechanges:=False


    'Delete the htm file we used in this function
    Kill TempFile


    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

 

[Kazdima]

Thank you. I am not using Outlook....I need just to consolidate= 30 reports of the same format from different files to combine(sumup) into One Master report. Many people mean under consolidation to put these 30 reports into one sheet as 30 different reports or into the same workbook as a separate sheet.

 

[jbeaucaire]

Thank you. I am not using Outlook....I need just to consolidate= 30 reports of the same format from different files to combine(sumup) into One Master report. Many people mean under consolidation to put these 30 reports into one sheet as 30 different reports or into the same workbook as a separate sheet.

I have "basic" macros that do either of these things in my code site collection.

'WORKBOOKS TO 1 SHEET STACKED
Here's a macro for collecting data from all files in a specific folder.

Workbooks to 1 Sheet​

The parts of the code that need to be edited are colored to draw your attention.

'WORKBOOKS W/MULTIPLE SHEETS MERGED INTO SHEETS IN CONSOLIDATION WORKBOOK
Here's a macro for collecting data from all sheets in all files in a specific folder merging into matching sheets.

Workbooks to Sheets - Part 4​

The parts of the code that need to be edited are colored to draw your attention.

'WORKBOOKS TO SHEETS
Here's a macro for collecting data from all files in a specific folder. This version copies the sheet in as a whole.

Workbooks to Sheets - PART 2​

The parts of the code that need to be edited are colored to draw your attention.

 

[Kazdima]

Thank you Jbeaucaire, correct me please if I am wrong, but it looks like that all your Macros do not consolidate(sum up) data into one report. They are pooling data from many places into one place and keep it. To my understanding, "Merge", "Combine" or "Consolidate"- should pull data from the files and sum up it based on common features. Like , for examples "Apples" in 5 different reports 1+1+1+1+1+1=5 in consolidated or combined or merged report.... At least- this what I am looking for, but five lines of Apple=1. Thank you, Dan

 

[jbeaucaire]

THese macros are consolidating data nto a single resource workbook, they are not summarizing it. Once the data is merged into a single sheet, for instance, using that as a source for your own Pivot Tables should be a simple expansion, something you might even add into the macro itself based on your pivot criteria.