Summarize multiple workbooks automatically located in same folder

[BKGLTS]

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

 

[BKGLTS]

Correct.
If I understand your code correctly, A1 must have a value of "Company Name".
The values of all the other cells is inconsequential.

Right now this is the format I am using to test your code.

Data Workbook format:
[TABLE="class: cms_table_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]Street Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Phone[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ACME Company[/TD]
[TD]123 Anywhere[/TD]
[TD]Austin[/TD]
[TD]Texas[/TD]
[TD]512-555-5555[/TD]
[/TR]
</tbody>[/TABLE]


I even tried these specific values including case sensitive and got the same result.
Data Workbook format:
[TABLE="class: cms_table_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]

Seems like no matter what I try the code just gives me "Company Name" in A1 and all other cells are blank.

 

[BKGLTS]

Think I figured out my mistake.
My root directory didn't have a backslash on the end.
ReTesting now.

 

[BKGLTS]

Seems to be working perfectly now!
Sorry for confusion caused by my error.
I cannot tell you how awesome this is.
You are a pro.
Thank you so much!
This will help me immensely.

 

[DanteAmor]

Seems to be working perfectly now!
Sorry for confusion caused by my error.
I cannot tell you how awesome this is.
You are a pro.
Thank you so much!
This will help me immensely.

Thanks for the feedback