get data from multiple files for the same sheet in one folder

[Abdo]

Hello

I need macro ot brings data from multiple files are existed in this directory "C:\Users\PC LP\Downloads\ Sales Report \" for the same sheet name" REPORT" for each file .

so my data in all of the files are different in locations . this means should match based on header even there are many duplicates data also should brings all of data from all of the files . the headers in file combined are A1: H1 in sheet REPORT where run the macro from it if they are matched with headers for the others files then should brings all of the files , if the headers are not matched , then should show message box and tell me what's the headers are not existed by indicate headers names in file combined . and every time run the macro should overwriting data by clearing data before brings them .
the files in the folder are xlsx and somtimes xlsm .

thank

 

[jdellasala]

Macro , Power Query ! Power Query is designed for just this kind of situation. It can pull in data from multiple files in multiple sheets and be transformed as needed - in most cases using just the UI and maybe using the UI to set up a manual tweak. It will save you HOURS of time, particularly over VBA. Great YouTube playlists on it here and here.

When posting, please use XL2BB for data, and the board code formatters (XLS, VBA, M (Power Query), etc.).

 

[Abdo]

actually i search for vba . I no know why you don't prefer vba

ok here is my data maybe somebody help .

file 1

sdf.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	CODE	DESCIBE	REF	DEL.N	QTY1	QTY2	CASE
2	1	CMTR1	BIG-DTR1	RE4	DL-12	45	200
3	2	CMTR2	BIG-DTR2	RE5	DL-13	67	300
4	3	CMTR3	BIG-DTR3	RE6	DL-14	87	400
5	4	CMTR4	BIG-DTR4	RE7	DL-15	45	500
6	5	CMTR5	BIG-DTR5	RE8	DL-16	34	0
7	6	CMTR6	BIG-DTR6	RE9	DL-17	3456	600
8	7	CMTR7	BIG-DTR7	RE10	DL-18	768	12
9	8	CMTR8	BIG-DTR8	RE11	DL-19	89	700
10	9	CMTR9	BIG-DTR9	RE12	DL-20	864	800
11	10	CMTR10	BIG-DTR10	RE13	DL-21	235	900
12	11	CMTR11	BIG-DTR11	RE14	DL-22	67	1000
13	12	CMTR12	BIG-DTR12	RE15	DL-23	543	1100
14	13	CMTR13	BIG-DTR13	RE16	DL-24	789	1200
15	14	CMTR14	BIG-DTR14	RE17	DL-25	457	1300
16	15	CMTR15	BIG-DTR15	RE18	DL-26	346	1400
REPORT

file 2

dfr.xlsx
	E	F	G	H	I	J	K	L
1	ITEM	CODE	DESCIBE	REF	DEL.N	QTY1	QTY2	CASE
2	1	CMTR11	BIG-DTR11	RE41	DL-121	4576	76
3	2	CMTR21	BIG-DTR21	RE51	DL-13	67	89
4	3	CMTR3	BIG-DTR3	RE6	DL-14	87	346
5	4	CMTR4	BIG-DTR4	RE7	DL-15	34	87
6	5	CMTR5	BIG-DTR5	RE8	DL-16	341	78
7	6	CMTR6	BIG-DTR6	RE9	DL-17	890	7890
8	7	CMTR7	BIG-DTR7	RE10	DL-18	90	433
9	8	CMTR8	BIG-DTR8	RE11	DL-19	876	700
10	9	CMTR9	BIG-DTR9	RE12	DL-20	45	800
11	10	CMTR10	BIG-DTR10	RE13	DL-21	67	900
12	12	CMTR12	BIG-DTR12	RE15	DL-23	98	1100
13	13	CMTR13	BIG-DTR13	RE16	DL-24	76	88
14	14	CMTR14	BIG-DTR14	RE17	DL-25	54	90
15	15	CMTR15	BIG-DTR15	RE18	DL-26	67	87
REPORT

file master before

master.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	CODE	DESCIBE	REF	DEL.N	QTY1	QTY2	CASE
2
3
4
5
6
7
8
9
10
11
12
13
14
15
REPORT

the result in file master

master.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	CODE	DESCIBE	REF	DEL.N	QTY1	QTY2	CASE
2	1	CMTR1	BIG-DTR1	RE4	DL-12	45	200
3	2	CMTR2	BIG-DTR2	RE5	DL-13	67	300
4	3	CMTR3	BIG-DTR3	RE6	DL-14	87	400
5	4	CMTR4	BIG-DTR4	RE7	DL-15	45	500
6	5	CMTR5	BIG-DTR5	RE8	DL-16	34	0
7	6	CMTR6	BIG-DTR6	RE9	DL-17	3456	600
8	7	CMTR7	BIG-DTR7	RE10	DL-18	768	12
9	8	CMTR8	BIG-DTR8	RE11	DL-19	89	700
10	9	CMTR9	BIG-DTR9	RE12	DL-20	864	800
11	10	CMTR10	BIG-DTR10	RE13	DL-21	235	900
12	11	CMTR11	BIG-DTR11	RE14	DL-22	67	1000
13	12	CMTR12	BIG-DTR12	RE15	DL-23	543	1100
14	13	CMTR13	BIG-DTR13	RE16	DL-24	789	1200
15	14	CMTR14	BIG-DTR14	RE17	DL-25	457	1300
16	15	CMTR15	BIG-DTR15	RE18	DL-26	346	1400
17	16	CMTR11	BIG-DTR11	RE41	DL-121	4576	76
18	17	CMTR21	BIG-DTR21	RE51	DL-13	67	89
19	18	CMTR3	BIG-DTR3	RE6	DL-14	87	346
20	19	CMTR4	BIG-DTR4	RE7	DL-15	34	87
21	20	CMTR5	BIG-DTR5	RE8	DL-16	341	78
22	21	CMTR6	BIG-DTR6	RE9	DL-17	890	7890
23	22	CMTR7	BIG-DTR7	RE10	DL-18	90	433
24	23	CMTR8	BIG-DTR8	RE11	DL-19	876	700
25	24	CMTR9	BIG-DTR9	RE12	DL-20	45	800
26	25	CMTR10	BIG-DTR10	RE13	DL-21	67	900
27	26	CMTR12	BIG-DTR12	RE15	DL-23	98	1100
28	27	CMTR13	BIG-DTR13	RE16	DL-24	76	88
29	28	CMTR14	BIG-DTR14	RE17	DL-25	54	90
30	29	CMTR15	BIG-DTR15	RE18	DL-26	67	87
REPORT

 

[jdellasala]

So many reasons to not like VBA. Aside from that it's verbose, has a terrible US, and difficult to code to get anything accomplished, its roots are in the earliest version of scripting for Office apps - before they were "Office" apps and in second place at best to the leaders in their fields - Lotus 123 for Spreadsheets, Word Perfect for Documents, and one whose name I can't remember for presentations.
Before I was forced into retirement due to COVID layoffs (obviously I've been doing this for a LONG time!) I was pulling data from a Mobile Device database with over 350K records and cross referencing that with data from four carriers - all in different layouts and formats. To reconcile one carrier could take more than a day. Then I discovered Power Query and had just started using it before I was let go. Had I learned even a bit of it sooner, I probably could have cut that down to less than an hour with only a little bit of PQ knowledge. That cannot be done with VBA.
I'll take a look at what you've posted (thanks for using XL2BB!) and see if I can help further.

 

[jdellasala]

So here's what PQ did in 21 minutes from scratch:

Book1
	A	B	C	D	E	F	G	H	I
1	Name	ITEM	CODE	DESCIBE	REF	DEL.N	QTY1	QTY2	CASE
2	file 1	1	CMTR1	BIG-DTR1	RE4	DL-12	45	200
3	file 1	2	CMTR2	BIG-DTR2	RE5	DL-13	67	300
4	file 1	3	CMTR3	BIG-DTR3	RE6	DL-14	87	400
5	file 1	4	CMTR4	BIG-DTR4	RE7	DL-15	45	500
6	file 1	5	CMTR5	BIG-DTR5	RE8	DL-16	34	0
7	file 1	6	CMTR6	BIG-DTR6	RE9	DL-17	3456	600
8	file 1	7	CMTR7	BIG-DTR7	RE10	DL-18	768	12
9	file 1	8	CMTR8	BIG-DTR8	RE11	DL-19	89	700
10	file 1	9	CMTR9	BIG-DTR9	RE12	DL-20	864	800
11	file 1	10	CMTR10	BIG-DTR10	RE13	DL-21	235	900
12	file 1	11	CMTR11	BIG-DTR11	RE14	DL-22	67	1000
13	file 1	12	CMTR12	BIG-DTR12	RE15	DL-23	543	1100
14	file 1	13	CMTR13	BIG-DTR13	RE16	DL-24	789	1200
15	file 1	14	CMTR14	BIG-DTR14	RE17	DL-25	457	1300
16	file 1	15	CMTR15	BIG-DTR15	RE18	DL-26	346	1400
17	file 2	1	CMTR11	BIG-DTR11	RE41	DL-121	4576	76
18	file 2	2	CMTR21	BIG-DTR21	RE51	DL-13	67	89
19	file 2	3	CMTR3	BIG-DTR3	RE6	DL-14	87	346
20	file 2	4	CMTR4	BIG-DTR4	RE7	DL-15	34	87
21	file 2	5	CMTR5	BIG-DTR5	RE8	DL-16	341	78
22	file 2	6	CMTR6	BIG-DTR6	RE9	DL-17	890	7890
23	file 2	7	CMTR7	BIG-DTR7	RE10	DL-18	90	433
24	file 2	8	CMTR8	BIG-DTR8	RE11	DL-19	876	700
25	file 2	9	CMTR9	BIG-DTR9	RE12	DL-20	45	800
26	file 2	10	CMTR10	BIG-DTR10	RE13	DL-21	67	900
27	file 2	12	CMTR12	BIG-DTR12	RE15	DL-23	98	1100
28	file 2	13	CMTR13	BIG-DTR13	RE16	DL-24	76	88
29	file 2	14	CMTR14	BIG-DTR14	RE17	DL-25	54	90
30	file 2	15	CMTR15	BIG-DTR15	RE18	DL-26	67	87
Sales Report

I got a little lost and forgot how to not delete the source file's name (the Name column after removing ".xlsx" from it). Once I remembered, I just had to select all columns and use Detect Data Type from the Transform tab.
Here's the actual code:

let
    Source = Folder.Files("C:\PC LP\Downloads\Sales Report"),
    LowercasedText = Table.TransformColumns(Source,{{"Extension", Text.Lower, type text}}),
    FilteredRows = Table.SelectRows(LowercasedText, each [Extension] = ".xlsx"),
    ReplacedDotXLSX = Table.ReplaceValue(FilteredRows,".xlsx","",Replacer.ReplaceText,{"Name"}),
    #"Filtered Hidden Files1" = Table.SelectRows(ReplacedDotXLSX, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",{"Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    ChangedType = Table.TransformColumnTypes(#"Expanded Table Column1",{{"ITEM", Int64.Type}, {"CODE", type text}, {"DESCIBE", type text}, {"REF", type text}, {"DEL.N", type text}, {"QTY1", Int64.Type}, {"QTY2", Int64.Type}, {"CASE", type any}})
in
    ChangedType

The first step is automatic - pulls in all the files from the specified folder. The next 3 steps are my cautionary steps to make sure I'm only getting the files I want and removed ".xlsx" from the File name. PQ automatically generated steps 5-8, and I manually did the ChangedType step. Additionally PQ generated a Parameter1 query, created a Sample File query, created a Transform File function, and created a Transform Sample File query (which is where changes to all files pulled in can be done).
I did run into one problem, however. Initially I named the Table in file 2 Table2, but that wasn't working because the function was only looking for Table1. I changed the name of the table and it worked fine. I could have changed the query to pull in the Worksheet itself, but that would have been locked to Sheet1. It would have been nearly identical and decided to take the cleaner route.
And it really took 21 minutes - that's when it said I posted my first reply when I started this one. In fact that includes time to create the folder structure and the two files! I suspect it took longer to make this post!

 

[Abdo]

thanks I tried implementing on my files but gives error for file name despite I using copy file name and paste to avoid error . I no know what's wrong !!!

 

[jdellasala]

thanks I tried implementing on my files but gives error for file name despite I using copy file name and paste to avoid error . I no know what's wrong !!!

I used the file path you had mentioned, so that shouldn't be a problem, and the query pulls in all the files, so filename isn't it either.
Edit the query, and when it opens an error message should appear at the top and a button to Go to Error. Click that, and if you don't understand it, let me know what step the problem is in.

 

[Abdo]

nd when it opens an error message should appear at the top and a button to Go to Error

I did it and inform me the file name is not available ,despite of I make sure is existed in the folder !

 

[jdellasala]

I did it and inform me the file name is not available ,despite of I make sure is existed in the folder !

The Query is to pull in ALL files in the folder - and subfolders, not just one. The first step presents you will ALL the files in the folder.

From there, you can filter both the Name and Extension columns to get only the files you need. I don't understand how you're getting an error that the FILENAME is not available. In the screenshot above, if you remove all the columns except Content and expand it, you'll be asked what element (Sheet or Table) you want to use.

This is where a problem can arise. If all the files don't have a table named Table1, those files will not combine. If they all have Sheet1, you can use that instead. The main difference is that with the table headers are set up properly. Using the Sheet, headers won't be promoted, but that's easy enough to clean up.