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!

 

[bmrr2]

Thank you, Jerry. That does help! But unfortunately the e-mails are not in my inbox and the people who need to access them do so from a shared drive. I'm not having much luck finding a way to save the attachments from e-mails that are already on a shared drive rather than in Outlook. Surely there's a way to look for that kind of code (there's got to be!), but I'm coming up with nothing (the codes that I've been seeing are for attachments for e-mails that are still in Outlook). Maybe you know of a way to search for that kind of thing?

 

[wallywalls3rd]

Jerry, looking at the responses on here you seem extremely knowledgeable about this subject. I am collecting data for a project at work. so far I have a workbook for each person to collect data and a page for each day of the month in those workbooks. at the end of the month I am copy pasting it into a master copy for that month. and again for a master copy for the quarter/year. I will be using this information to track trends using pivot charts and tables. is there a macro that I can use that would pull all of the data to the next "master" sheet? kind of like a pyramid. so the employees would pull to the month, the month would pull to the quarter, and the quarter would pull to the year? I am newer to excel and have done all of this on my own. but I am not macro savvy in the slightest. but I have some coworkers that can help me implement it if I were to have the macro coding itself.

 

[Valley]

Thank you very much!

 

[Valley]

I have a macro for taking the data from a database sheet and filling out a template sheet over and over again with the data.

Data Into Template​

The macro fills out the template and saves each one as a separate file OR creates a separate sheet, your choice. That last part can be tweaked to do other things, this is mainly to demonstrate a simple way to fill out a form from a row-based database.


------------------------

Here's a macro for taking a sheet with data and creating individual wbs from each unique value in a chosen column. The date is added to the workbook names to give a reference as to when the wbs were created.

SHEET to WORKBOOKS(1)​

-----------------
Perhaps you can take the concepts outlined in those two macros and merge them into one?

Hello Jerry,

I am highly appreciated your help, now I have total 100 excel files, there are serveral sheets in these excel files, but there is common sheet named "PBA", I only retrieve cell G10's value from sheet "PBA", how to do?

Best regards,
Valley

 

[jbeaucaire]

Something like this to get that one value without having to open the files:

Option Explicit

Sub CollectFromFiles()
Dim fPATH As String, fNAME As String
Dim NR As Long, wsDEST As Worksheet

Set wsDEST = ThisWorkbook.Sheets("Summary")
NR = wsDEST.Range("B" & Rows.Count).End(xlUp).Row + 1

fPATH = "C:\2017\DataFiles\"                'path to files, remember the final \ in this string

fNAME = Dir(fPATH & "*.xls*")               'get the first filename in fpath

Do While Len(fNAME) > 0
        
    wsDEST.Range("A" & NR) = fNAME          'put the filenmae in column A, value in column B
    With wsDEST.Range("B" & NR)
        .Formula = "='" & fPATH & "[" & fNAME & "]PBA'!$G$10"
        .Value = .Value
    End With
        
    NR = NR + 1                             'next output row
    fNAME = Dir                             'get the next filename

Loop

End Sub

 

[Valley]

Something like this to get that one value without having to open the files:

Option Explicit

Sub CollectFromFiles()
Dim fPATH As String, fNAME As String
Dim NR As Long, wsDEST As Worksheet

Set wsDEST = ThisWorkbook.Sheets("Summary")
NR = wsDEST.Range("B" & Rows.Count).End(xlUp).Row + 1

fPATH = "C:\2017\DataFiles\"                'path to files, remember the final \ in this string

fNAME = Dir(fPATH & "*.xls*")               'get the first filename in fpath

Do While Len(fNAME) > 0
        
    wsDEST.Range("A" & NR) = fNAME          'put the filenmae in column A, value in column B
    With wsDEST.Range("B" & NR)
        .Formula = "='" & fPATH & "[" & fNAME & "]PBA'!$G$10"
        .Value = .Value
    End With
        
    NR = NR + 1                             'next output row
    fNAME = Dir                             'get the next filename

Loop

End Sub

thank you very much, Jerry, I already quickly get all these data due to your support and contribution, many thanks!!!

 

[rac241985]

Hi Jerry - Can you please guide me on how to combine data from multiple workbooks into one single workbook ? I want data from a specific sheet from the excel files and all the files are in one folder.

 

[jbeaucaire]

Here's a macro for collecting the data from the main sheet in all the workbooks in a given folder.

Workbooks to 1 Sheet

The main macro takes the date from the main sheet, but you could add one line of code to make it switch to the specifically needed sheet before it did it's copying stuff:

        'This is the section to customize, replace with your own action code as needed
            Sheets("Sheet3").Activate
            LR = Range("A" & Rows.Count).End(xlUp).Row  'Find last row

 

[rac241985]

Jerry - great code! Its working really great for me. Thanks a lot.

I modified the contents to suit my needs but have one small problem. I have first 2 rows as the header in each file. However, when I am running the macro it appends the header from each file. I basically want the macro to extract the header from the first file after which the code should start from Row 3 for the remaining files. I know it might be a small tweak but not sure what I am missing. Can you please help out?

Here is my slightly modified code

'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
            Sheets("Change Orders").Activate
            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 'Next row
            End If
        fName = Dir                                       'ready next filename
    Loop
End With

 

[jbeaucaire]

On my phone, so I can't type a lot. You should be able to spot the COPY command, it has a reference to A1, change that to A3?