Excel masterfile vba

[moshc]

Good day!

Anyone can help me please?

I've been looking for an EXCEL VBA code in which i will have the option to select a folder path first which all the workbooks i need to combined is saved and have all the first sheets in all workbook in that specific folder is then combined to a new workbook.

Would really much appreciate any immediate response.

Thank you!

 

[jskasango]

When I tested the macro using a couple of dummy workbooks, it worked properly. The output is directed to Sheet1 of the workbook containing the macro. It is hard for me to see why it is not working for you because I don't have access to your actual files. Check to make sure that all the source workbooks have a sheet named ""EntryList".

Do you mind if I give you my screen on Teamviewer?

 

[mumps]

I would prefer if you could upload your destination file and at least two or three of your source files to a free file sharing site and post the link here to the files (de-sensitized if necessary)

 

[jskasango]

When I tested the macro using a couple of dummy workbooks, it worked properly. The output is directed to Sheet1 of the workbook containing the macro. It is hard for me to see why it is not working for you because I don't have access to your actual files. Check to make sure that all the source workbooks have a sheet named ""EntryList".

Do you mind if I give you my screen on Teamviewer?

 

[Fluff]

@jskasango please do not offer to contact members privately in any way, as it is against the rules.
All communication must remain on the board.
Thanks

 

[jskasango]

Noted. Sorry

 

[jskasango]

@mumps I am plasing 3 XL2BB minisheets here for you to help me test the code. You can have one or two of them in a folder and the other/s in a subfolder.
In my computer, they sit at G:\CIMKEN\FBK\KB20B with a subfolder \TEST.

0B01.xls
	A	B	C	D							K															Z		AB	AC	AD	AE		AG	AH	AI	AJ	AK	AL
1	Entry	StockID	Name	BreedersPedigree1							Origin															nEarsSelected		nVirusMaizeStreak1_5(1)	nGrayLeafSpot1_5(1)	nLeafBlightTurcicum1_5(1)	nCommonRust1_5(1)		nEarAspect1_5	nGrainTexture1_5	nAnthesisDate	nHeteroticGroup	nNumRows
2	ENT	STKID	CODE	PED1							ORI															ESL		MSV1	GLS1	ET1	PS1		EA	TEX	AD
3	NumberFormat																									0		0.0	0.0	0.0	0.0		0.0	0.0	0.0	Text	0	0.0
4	NumObs
5	Average
6
18
19
20
21
22
23	Entry	Stock	Name	Pedigree							Origin															Ears		MSV	GLS	E.turc	P.sorg		Ear	Grain	Anth	Het	Num	Silk
24		ID																								Select							Asp	Tex	Date	Group	Rows	Date
25	1	D1565-5	dfadfg	afafhahhf							dfhafdh															4
26	2	D1565-7	fdgad	fhafdhadfha							dfhafdh															5
27	3	D1565-8	dfghadfh	fdahafdhadfh							hadfh															7
28	4	D1565-10	dafag	hadfhafhafdhafd							adhf															6
29	5	D1565-10	dafag	hadfhafhafdhafd							adhf															6
30	6	D1565-5	dfadfg	afafhahhf							dfhafdh															7.1
31	7	D1565-7	fdgad	fhafdhadfha							dfhafdh															7.6
32	8	D1565-8	dfghadfh	fdahafdhadfh							hadfh															8.1
Entrylist

0A50.xls
	A	B	C	D							K															Z		AB	AC	AD	AE	AF	AG	AH	AI
1	Entry	StockID	Name	BreedersPedigree1							Origin															nEarsSelected		nVirusMaizeStreak1_5(1)	nGrayLeafSpot1_5(1)	nLeafBlightTurcicum1_5(1)	nCommonRust1_5(1)	nPhaesLeafSpot1_5(1)	nEarAspect1_5	nGrainTexture1_5	nAnthesisDate
18
19
20
21
22
23	Entry	Stock	Name	Pedigree							Origin															Ears		MSV	GLS	E.turc	P.sorg	Phaeo	Ear	Grain	Anth
24		ID																								Select							Asp	Tex	Date
25	1	D1449-53	weqgtqer	etqertqert							gdgagagf															10
26	2	D1179-99	ertqert	tertertqert							gsd															4
27	3	D1293-85	ertert	tertertqert							dsgga															9
28	4	D1179-101	ertret	teertertqer							gadg															1
29	5	D1411-12	ertert	dfgdfgd							dgdfgd															2
30
Entrylist

0B02.xls
	A	B	C	D							K															Z		AB	AC	AD	AE		AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1	Entry	StockID	Name	BreedersPedigree1							Origin															nEarsSelected		nVirusMaizeStreak1_5(1)	nGrayLeafSpot1_5(1)	nLeafBlightTurcicum1_5(1)	nCommonRust1_5(1)		nEarAspect1_5	nGrainTexture1_5	nAnthesisDate	nHeteroticGroup	nNumRows						nTryptophanPer_WholeGrain
18
19
20
21																																							CIMMYT
22
23	Entry	Stock	Name	Pedigree							Origin															Ears		MSV	GLS	E.turc	P.sorg		Ear	Grain	Anth	Het	Num	Silk	Notes
24		ID																								Select							Asp	Tex	Date	Group	Rows	Date
25	1	dgdg	gsdg	gsD							gsdg															9																	W
26	2	D1692-2	sdg	SDGsdgSDG							gsdg															5																	Y
27	3	dsg	sdg	sdg							gsdg															9																	W
28	4	sdg	DL173850	sdggsdg							sgd															5																	W
29	5	D1692-5	sdg	DGSGsdggd							sdg															6																	W
30	6	dgdg	gsdg	gsD							gsdg															5
31	7	D1692-3	sdg	SDGsdgSDG							gsdg															4
32	8	dsg	sdg	sdg							gsdg															4
33	9	sdg	DL173851	sdggsdg							sgd															3
34	10	dgdg	gsdg	gsD							gsdg															3
Entrylist

Cells with Data Validation
Cell	Allow	Criteria
AA1:AQ1	List	=nNur

It is my hope that this is permitted. Thanks.

 

[mumps]

I placed the first file in one folder and the second two files in a sub folder of the first. This was the result:

Book5
	A	B	C	D	E	F	G	H	I	J	K	L
1	NurseryName	Entry	StockID	Name	BreedersPedigree1							Origin
2	Test-jskasango1	1	D1565-5	dfadfg	afafhahhf							dfhafdh
3	Test-jskasango1	2	D1565-7	fdgad	fhafdhadfha							dfhafdh
4	Test-jskasango1	3	D1565-8	dfghadfh	fdahafdhadfh							hadfh
5	Test-jskasango1	4	D1565-10	dafag	hadfhafhafdhafd							adhf
6	Test-jskasango1	5	D1565-10	dafag	hadfhafhafdhafd							adhf
7	Test-jskasango1	6	D1565-5	dfadfg	afafhahhf							dfhafdh
8	Test-jskasango1	7	D1565-7	fdgad	fhafdhadfha							dfhafdh
9	Test-jskasango1	8	D1565-8	dfghadfh	fdahafdhadfh							hadfh
10	Testjs-jskasango2	1	D1449-53	weqgtqer	etqertqert							gdgagagf
11	Testjs-jskasango2	2	D1179-99	ertqert	tertertqert							gsd
12	Testjs-jskasango2	3	D1293-85	ertert	tertertqert							dsgga
13	Testjs-jskasango2	4	D1179-101	ertret	teertertqer							gadg
14	Testjs-jskasango2	5	D1411-12	ertert	dfgdfgd							dgdfgd
15	Testjs-jskasango3	1	dgdg	gsdg	gsD							gsdg
16	Testjs-jskasango3	2	D1692-2	sdg	SDGsdgSDG							gsdg
17	Testjs-jskasango3	3	dsg	sdg	sdg							gsdg
18	Testjs-jskasango3	4	sdg	DL173850	sdggsdg							sgd
19	Testjs-jskasango3	5	D1692-5	sdg	DGSGsdggd							sdg
20	Testjs-jskasango3	6	dgdg	gsdg	gsD							gsdg
21	Testjs-jskasango3	7	D1692-3	sdg	SDGsdgSDG							gsdg
22	Testjs-jskasango3	8	dsg	sdg	sdg							gsdg
23	Testjs-jskasango3	9	sdg	DL173851	sdggsdg							sgd
24	Testjs-jskasango3	10	dgdg	gsdg	gsD							gsdg
Sheet1

 

[jskasango]

SOLVED!!!!!!
My WBS were old!!! I edited this ===== If Right(oFile, 4) Like "xls*" Then to this ====== If Right(oFile, 3) Like "xl*" Then .

I am happy, very happy. Sorry for the embarrassment I have caused.

 

[mumps]

Glad it worked out.

 

[jskasango]

@mumps .Wow! I was up the whole night just watching the code crunch the data. That would have taken me several MONTHS northwards!
Now I do not know where to say "If Sheet(EntryList) has no data, Skip and go to the next WB with a Sheet(EntryList)". I will be happy if it can do this because it stops executing when it encounters a Sheet(EntryList) that has no data. To proceed smoothly, I have been moving away such Workbooks and rerunning the code. Thanks in advance! - Kasango.