Summarize multiple workbooks automatically located in same folder

B

BKGLTS

Board Regular
Joined
Aug 27, 2018
Messages
82
Hello,

I need help please.

Say I have 3 identically formatted data workbooks with different data in each, but all located in the same folder.

Data Workbook1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name:[/TD]
[TD]Company A
[/TD]
[/TR]
[TR]
[TD]Location:[/TD]
[TD]Texas[/TD]
[/TR]
[TR]
[TD]Task 1:[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Task 2:[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Task 3:[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Task 4:[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

Data Workbook2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name:[/TD]
[TD]Company B
[/TD]
[/TR]
[TR]
[TD]Location:[/TD]
[TD]Ohio[/TD]
[/TR]
[TR]
[TD]Task 1:[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Task 2:[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Task 3:[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Task 4:[/TD]
[TD]Yes
[/TD]
[/TR]
</tbody>[/TABLE]

Data Workbook3:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name:[/TD]
[TD]Company C
[/TD]
[/TR]
[TR]
[TD]Location:[/TD]
[TD]Virginia[/TD]
[/TR]
[TR]
[TD]Task 1:No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Task 2:[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Task 3:[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Task 4:[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]


Then I have a 4th workbook, in the same folder, for summarizing the 3 data workbooks.
Summary Workbook:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name:[/TD]
[TD]Company A[/TD]
[TD]Company B[/TD]
[TD]Company C[/TD]
[/TR]
[TR]
[TD]Location:[/TD]
[TD]Texas[/TD]
[TD]Ohio[/TD]
[TD]Virginia[/TD]
[/TR]
[TR]
[TD]Task 1:[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Task 2:[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Task 3:[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Task 4:[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]


Simple enough, but I need the Summary Workbook to auto populate/update whenever a new data workbook is dropped into the same folder.
So, if I drop a 4th data workbook, in the same folder, formatted the same as the others, the Summary Workbook would automatically see it and create a new 5th column for it and fill the data in accordingly.

Thank you!

Sincerely,
B
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Could you put the rows and columas of excel to know where each data is (name, location, task)?
And also in what rows and columns you want the summary.
The summary book will not be automatically updated every time you put or remove a book from the folder, the summary will be updated when an event occurs in the summary book, or you press a button, it may be when you open the summary book, then run it the process in automatic.
 
Upvote 0
I am going to have hundreds of these data workbooks in the folder. I am looking for a way for it to automatically go out and see all of those and drop them into the summary workbook if they exist in the folder. I dont have time to manually point to each range of cells in each data workbook. It would be fine if it was triggered to update from an event such as a button click or opening of the summary workbook.
 
Upvote 0
I just wanted to know the format of one of your files. And the format of your summary file.
I will also assume that the information is on the first page of each book.


I give you a macro example to read your hundreds of books and you adapt the macro.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Summary sheet:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 80, align: left"][/TD]
[TD="width: 80, align: center"]A[/TD]
[TD="width: 80, align: center"]B[/TD]
[TD="width: 80, align: center"]C[/TD]
[TD="width: 80, align: center"]D[/TD]
[/TR]
[TR]
[TD="width: 80, align: left"]1[/TD]
[TD="width: 80, align: left"]Name:[/TD]
[TD="width: 80, align: left"]Company A[/TD]
[TD="width: 80, align: left"]Company B[/TD]
[TD="width: 80, align: left"]Company C[/TD]
[/TR]
[TR]
[TD="align: left"]2[/TD]
[TD="align: left"]Location:[/TD]
[TD="align: left"]Texas[/TD]
[TD="align: left"]Ohio[/TD]
[TD="align: left"]Virginia[/TD]
[/TR]
[TR]
[TD="align: left"]3[/TD]
[TD="align: left"]Task 1:[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: left"]No[/TD]
[/TR]
[TR]
[TD="align: left"]4[/TD]
[TD="align: left"]Task 2:[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: left"]No[/TD]
[/TR]
[TR]
[TD="align: left"]5[/TD]
[TD="align: left"]Task 3:[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: left"]Yes[/TD]
[/TR]
[TR]
[TD="align: left"]6[/TD]
[TD="align: left"]Task 4:[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: left"]Yes

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Format Data Workbook

[TABLE="class: grid, width: 500"]
<colgroup><col width="80" span="2" style="width:60pt"> </colgroup><tbody>[TR]
[TD="width: 80, align: left"][/TD]
[TD="width: 80, align: center"]A[/TD]
[TD="width: 80, align: center"]B[/TD]
[/TR]
[TR]
[TD="width: 80, align: left"]1[/TD]
[TD="width: 80, align: left"]Name:[/TD]
[TD="width: 80, align: left"]Company A[/TD]
[/TR]
[TR]
[TD="width: 80, align: left"]2[/TD]
[TD="width: 80, align: left"]Location:[/TD]
[TD="width: 80, align: left"]Texas[/TD]
[/TR]
[TR]
[TD="width: 80, align: left"]3[/TD]
[TD="width: 80, align: left"]Task 1:[/TD]
[TD="width: 80, align: left"]Yes[/TD]
[/TR]
[TR]
[TD="width: 80, align: left"]4[/TD]
[TD="width: 80, align: left"]Task 2:[/TD]
[TD="width: 80, align: left"]Yes[/TD]
[/TR]
[TR]
[TD="width: 80, align: left"]5[/TD]
[TD="width: 80, align: left"]Task 3:[/TD]
[TD="width: 80, align: left"]No[/TD]
[/TR]
[TR]
[TD="width: 80, align: left"]6[/TD]
[TD="width: 80, align: left"]Task 4:[/TD]
[TD="width: 80, align: left"]No[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

---
Code: 
Sub macro_ene19_001()
    'Summarize multiple workbooks automatically located in same folder
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.StatusBar = False
    '
    Set l1 = ThisWorkbook
    Set h1 = l1.Sheets(1)
    '
    ruta = "C:\trabajo\books\"  'folder name
    comp = "B1"                 'cell with company
    loca = "B2"                 'cell with location
    '
    h1.Cells.ClearContents
    h1.Range("A1").Value = "Name:"
    h1.Range("A2").Value = "Location:"
    arch = Dir(ruta & "*.xls*")
    n = 1
    '
    Do While arch <> ""
        Application.StatusBar = "Processing book : " & n
        If LCase(arch) <> LCase(l1.Name) Then
            Set l2 = Workbooks.Open(ruta & arch)
            Set h2 = l2.Sheets(1)
            company = h2.Range(comp)
            locatio = h2.Range(loca)
            existe = False
            col = h1.Cells(1, Columns.Count).End(xlToLeft).Column + 1
            Set r = h1.Rows(1)
            Set b = r.Find(company, lookat:=xlWhole)
            If Not b Is Nothing Then
                celda = b.Address
                Do
                    If h1.Cells(2, b.Column).Value = Location Then
                        existe = True
                        col = b.Column
                        Exit Do
                    End If
                    Set b = r.FindNext(b)
                Loop While Not b Is Nothing And b.Address <> celda
            End If
            If existe = False Then
                h1.Cells(1, col).Value = company
                h1.Cells(2, col).Value = locatio
            End If
            u2 = h2.Range("A" & Rows.Count).End(xlUp).Row
            For i = 3 To u2
                task = h2.Cells(i, "A").Value
                Set b = h1.Columns("A").Find(task, lookat:=xlWhole)
                If Not b Is Nothing Then
                    h1.Cells(b.Row, col).Value = h2.Cells(i, "B").Value
                Else
                    u1 = h1.Range("A" & Rows.Count).End(xlUp).Row + 1
                    h1.Cells(u1, "A").Value = task
                    h1.Cells(u1, col).Value = h2.Cells(i, "B").Value
                End If
            Next
            l2.Close False
        End If
        n = n + 1
        arch = Dir()
    Loop
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub

Put the macro in the summary book.


I hope it helps you
 
Upvote 0
DanteAmor,

After considering the usability I need to change the formats to the following:

Data Workbook format:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]COMPANY NAME[/TD]
[TD]TASK 1[/TD]
[TD]TASK 2[/TD]
[TD]TASK 3[/TD]
[TD]TASK 4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ACME Company[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]


Summary Workbook format:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]COMPANY NAME[/TD]
[TD]TASK 1[/TD]
[TD]TASK 2[/TD]
[TD]TASK 3[/TD]
[TD]TASK 4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ACME Company[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No
[/TD]
[/TR]
</tbody>[/TABLE]


Each additional data workbook would get its own row in the summary workbook.


I tried your code, and changed the root directory and it worked perfect.
Could you please modify to the new format?

Thank you!

Sincerely,
B
 
Last edited:
Upvote 0
Thank you so much DanteAmor!
I need to clarify that the Data Workbooks will have 300 columns of data that need to be summarized in the Summary Workbook.
The above is just an example.
If you could please make the code reflect this it would be extremely helpful.

Thank you so much!
This is so unbelievably helpful and I am so surprised it can be done.

Sincerely,
B
 
Upvote 0
Try this

Code: 
Sub macro_ene22_002()
    'Summarize multiple workbooks automatically located in same folder
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.StatusBar = False
    '
    Set l1 = ThisWorkbook
    Set h1 = l1.Sheets(1)
    '
    ruta = "C:\trabajo\books\"  'folder name
    comp = "A2"                 'cell with company
    '
    h1.Cells.ClearContents
    h1.Range("A1").Value = "Company Name"
    arch = Dir(ruta & "*.xls*")
    n = 1
    '
    Do While arch <> ""
        Application.StatusBar = "Processing book : " & n
        If LCase(arch) <> LCase(l1.Name) Then
            Set l2 = Workbooks.Open(ruta & arch)
            Set h2 = l2.Sheets(1)
            company = h2.Range(comp)
            existe = False
            fila = h1.Range("A" & Rows.Count).End(xlUp).Row + 1
            Set r = h1.Columns("A")
            Set b = r.Find(company, LookAt:=xlWhole)
            If Not b Is Nothing Then
                celda = b.Address
                Do
                    If h1.Cells(2, b.Column).Value = Location Then
                        existe = True
                        fila = b.Row
                        Exit Do
                    End If
                    Set b = r.FindNext(b)
                Loop While Not b Is Nothing And b.Address <> celda
            End If
            If existe = False Then
                h1.Cells(fila, "A").Value = company
            End If
            uc = h2.Cells(1, Columns.Count).End(xlToLeft).Column
            For j = 2 To uc
                task = h2.Cells(1, j).Value
                Set b = h1.Rows(1).Find(task, LookAt:=xlWhole)
                If Not b Is Nothing Then
                    h1.Cells(fila, b.Column).Value = h2.Cells(2, j).Value
                Else
                    uc1 = h1.Cells(1, Columns.Count).End(xlToLeft).Column + 1
                    h1.Cells(1, uc1).Value = task
                    h1.Cells(fila, uc1).Value = h2.Cells(2, j).Value
                End If
            Next
            l2.Close False
        End If
        n = n + 1
        arch = Dir()
    Loop
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub
 
Upvote 0
DanteAmor,

Thank you so much for helping me.
I replaced the directory path, but this code is only placing the words "Company Name" in cell A1.
No other cells are populating.

Here is the first seven columns of the actual data workbook.

[TABLE="class: grid, width: 723"]
<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Company Name[/TD]
[TD]Street Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Phone[/TD]
[TD]Fax[/TD]
[TD]Parent Company Name[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]123 Anywhere[/TD]
[TD]Austin[/TD]
[TD]Texas[/TD]
[TD](512) 123-4567[/TD]
[TD](512) 123-4567[/TD]
[TD]Group A[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!
B
 
Upvote 0
Your books should respect this structure:

Data Workbook format:
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]COMPANY NAME[/TD]
[TD]TASK 1[/TD]
[TD]TASK 2[/TD]
[TD]TASK 3[/TD]
[TD]TASK 4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ACME Company[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Moving and renaming multiple workbooks from a master workbook.
Replies
1
Views
267
Luke1690
L
V
  • Question Question
Multiple linked tables that can all be edited, and automatically update across all?
Replies
0
Views
68
VikingLink
V
T
  • Solved
Copy and Past the Data from Multiple Closed Workbooks
2
Replies
16
Views
1K
thespardian
T
G
  • Question Question
remove columns from multiple Excel-files in a folder
Replies
5
Views
335
gjvv
G
S
  • Solved
Summarize base data
Replies
5
Views
223
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top