Combine All Excel files in one folder daily

[BVL]

Hi All,
We have an Excel overview of all the orders our customers create. These orders create a to do list for our drafters to create productdrawings. This overview is updated daily by extracting data from our ERP system, new orders are added and finished orders are deleted. Orders that are not finished get an increment of "+1" in column L "#days in" (=number of days in the list).
I would like to keep track of the number of days our drafters (and other teammembers) need to create those drawings on a monthly basis, I want to use this to create transparency within our company of the average processing time. Can you help me?
Setup:
The drafters extract the orders on a daily basis from our ERP system and update a list that looks like <"Drafter list_Master.xlsx" sheet:"list">. By executing that macro action they also create a copy (archive) to "S:\Drafters\List\Archive". This folder contains hundreds of files named "Drafter list_date x.xlsx" (-> date x= 12/12/2019, 13/12/2019, 14/12/2019, and so on)
I would like to keep track of the "#days in", so we can see when the workload explodes and when we need to add drafters to cope with the workload.
I've added a picture "Pivotchart" that is linked to "Result" that shows what I'm after: a graph that visualizes the average "#days in" and the maximum "#days in". [column F checks whether the order is still in process or finished, but there are probably better ways?]
The tricky part for me is the combination of all those files into 1 file? Possibly the macro that extracts data and creates an archive copy could be a good way to go, though a separate macro and file is also an option.

My level: I have no experience in writing macros, but I have basic knowledge in SQL.

<"Drafter list_Master.xlsx" sheet:"list">

Item Number	DRAWING	Name	P/M	St	Order	Ln	Ext Price	Qty Ordered	Remarks	Prom	#days in
10	101	aa		NC	S2190967	10	1.00	1.0	CSP	13-Dec-19	25
11	102	bb		nc	B2191415	1	1.00	10.0		30-Dec-19	6
12	103	aa		nc	S2191415	2	1.00	10.0		30-Dec-19	6
13	104	bb		N	S2191486	1	1.00	5.0		13-Jan-20	3
14	105	aa		N	B2191486	2	1.00	5.0		13-Jan-20	3
15	106	bb		N	S2191486	3	1.00	5.0		13-Jan-20	3
16	107	aa		N	S2191459	1	1.00	2.0		17-Jan-20	3
17	108	bb		N	S2191462	35	1.00	29.0		20-Jan-20	3
18	109	aa		N	T2191478	10	1.00	6.0		20-Jan-20	3
19	110	bb		N	S2191478	270	1.00	5.0		20-Jan-20	3
20	111	aa		N	S2191478	20	1.00	6.0		20-Jan-20	3
21	112	bb		N	S2191478	280	1.00	5.0		20-Jan-20	3
22	113	aa		NC	S2191462	23	1.00	20.0		20-Jan-20	3
23	114	bb		NC	S2191462	21	1.00	5.0		20-Jan-20	3
24	115	aa		N	S2191462	8	1.00	5.0		20-Jan-20	3
25	116	bb		N	T2191462	1	1.00	3.0		20-Jan-20	3
26	117	aa		NC	S2191480	10	1.00	1.0	CSP	27-Jan-20	3
27	118	bb		NC	S2191475	68	1.00	10.0		25-Mar-20	3
28	119	aa		N	S2191475	101	1.00	75.0		25-Mar-20	3
29	120	bb		NC	S2191475	140	1.00	10.0		25-Mar-20	3
30	121	aa		NC	B2191475	167	1.00	5.0		25-Mar-20	3
31	122	bb		NC	B2191475	21	1.00	90.0		25-Mar-20	3
32	123	aa		NC	S2191475	25	1.00	5.0		25-Mar-20	3
33	124	bb		NC	S2191475	15	1.00	20.0		25-Mar-20	3
34	125	aa		NC	S2191482	1	1.00	2.0	General	16-Jan-20	2

<"Drafter list_Master.xlsx" sheet:"Result">

Date	Order	Name	Prom	#days in	Finish date ("=IF(G2=B2,"",A2)")	"=IFERROR(VLOOKUP(B2,B3:B19,1,FALSE),A2)"
16/12/2019​	B2191415	bb	30-Dec-19	6	16/12/2019​	16/12/2019​
16/12/2019​	B2191486	aa	13-Jan-20	3		B2191486
16/12/2019​	T2191478	aa	20-Jan-20	3		T2191478
16/12/2019​	T2191462	bb	20-Jan-20	3		T2191462
16/12/2019​	B2191475	aa	25-Mar-20	3		B2191475
16/12/2019​	B2191475	bb	25-Mar-20	3		B2191475
17/12/2019​	B2191486	aa	13-Jan-20	4	17/12/2019​	17/12/2019​
17/12/2019​	T2191478	aa	20-Jan-20	4	17/12/2019​	17/12/2019​
17/12/2019​	T2191462	bb	20-Jan-20	4	17/12/2019​	17/12/2019​
17/12/2019​	B2191475	aa	25-Mar-20	4		B2191475
17/12/2019​	B2191475	bb	25-Mar-20	4		B2191475
17/12/2019​	T2201234	cc	20-Jan-20	1		T2201234
18/12/2019​	B2191475	aa	25-Mar-20	5		B2191475
18/12/2019​	B2191475	bb	25-Mar-20	5	18/12/2019​	18/12/2019​
18/12/2019​	T2201234	cc	20-Jan-20	2	18/12/2019​	18/12/2019​
18/12/2019​	B2201235	cc	21-Jan-20	1	18/12/2019​	18/12/2019​
18/12/2019​	B2201236	cc	22-Jan-20	1	18/12/2019​	18/12/2019​
18/12/2019​	B2201237	cc	23-Jan-20	1	18/12/2019​	18/12/2019​

 

[sandy666]

try Power Query (Get&Transform) with From Folder option

 

[BVL]

@sandy666 Power Query works like a charm! But now I've got a small fine-tuning question. I now have a long list that looks like <"Drafter list_Master.xlsx" sheet:"Result"> except the first column "Date", which I use to combine values in the Pivot Chart. In my source file I have the column "Source.Name" which I use together with "Add column>from text>extract>range" to extract the date "ddmmyyyy". For easier manipulation in excel (and the Pivot Chart) I'd like to convert this text "ddmmyyyy" to a date "dd/mm/yyyy", but using the power query function "transform>data type: date" gives an Error. Are there (easy) workarounds in Power Query?
I'd prefer not to do this in Excel since now I can just do a simple refresh in Excel to update my data instead of performing additional actions to clean up that data. (every day)

 

[BVL]

See power query sample below

 

[sandy666]

you can adapt this code

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"DayMonthYear", type text}}),
    Insert = Table.AddColumn(Table.AddColumn(Type, "Custom", each Text.Insert([DayMonthYear],2,"/")), "Custom.1", each Text.Insert([Custom],5,"/")),
    Date = Table.TransformColumnTypes(Table.RenameColumns(Table.SelectColumns(Insert,{"Custom.1"}),{{"Custom.1", "DayMonthYear"}}),{{"DayMonthYear", type date}})
in
    Date

then move result column to correct place

 

[sandy666]

or better this one

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"DayMonthYear", type text}}),
    Insert = Table.AddColumn(Table.AddColumn(Type, "Custom", each Text.Insert([DayMonthYear],2,"/")), "Custom.1", each Text.Insert([Custom],5,"/")),
    DMY = Table.RenameColumns(Table.TransformColumnTypes(Table.RemoveColumns(Insert,{"DayMonthYear", "Custom"}),{{"Custom.1", type date}}),{{"Custom.1", "DayMonthYear"}})
in
    DMY

 

[BVL]

thanks!

 

[sandy666]

You are welcome
Hope it will work for you
Have a nice day