Consolidate Multiple Worksheets into one Worksheet

[JohanGduToit]

Greetings Forum,

I receive Excel Files containing customer sales information on a weekly basis which I have to import into MS Access. I have to reformat them prior to importing. Each file consists of four worksheets and each worksheet contain the same number of columns (A to X) (each worksheet has various row counts).

I need to consolidate the four worksheets into one 'Consolidated' worksheet within the same Excel file.

Before consolidating the four worksheets, the following must happen on each of these sheets:

(a) Rows 1 to 3 must be deleted
(b) Columns on each of the worksheets run from "A" to "X"; but I am only interested in consolidating (copying) columns A, B, C, D, H and N (all the other columns may be ignored).

Once consolidated, the four original worksheets should be deleted so that only the 'Consolidated' worksheet remain (without any column headers).

The VBA procedure will reside within my MS Access application and will be called using the command : Call FormatMrPSport(fName) where fName will be the name of the Excel File to be processed.

Any help to assist me with some sample code would be greatly appreciated. My current code is designed to use late binding and what I have successfully deletes top 3 rows and deletion of columns; but failing on the consolidation part.

Many thanks!!

 

[JohanGduToit]

SalesFile.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1							T/Y Open SOH	T/Y Sales	T/Y Ave Week Sales	T/Y Close SOH	T/Y SIT	T/Y	T/Y Open SOH	T/Y Sales	T/Y Ave Week Sales	S to S	T/Y Close SOH	T/Y SIT	Cover	Stock	Sales	T/Y PMO	T/Y GP	T/Y GP
2	Brc	Branch Description	Item Number	Item Description	End Use		Units	Units	Units	Units	Units	Clearance %	Value	Value	Value	Ratio	Value	Value		Cont	Cont	%	Value	%
3							22706	1713	1713	22318	50	7.13	4183392.94	296762.86	296762.86	14.1	4142246.82	11629.5	13.03	100	100	48.16	119999.83	46.5
4	100	Mr Price Sport Online	6701010271	Mxd Mns Digital LED watch blk			2		0	2	0	0	259.98		0	0	259.98	0	0	0.01	0	0	0	0
5	100	Mr Price Sport Online	6701010306	Mens square Digi watch black			1		0	1	0	0	9.99		0	0	9.99	0	0	0	0	0	0	0
6	100	Mr Price Sport Online	6701010395	Mxd Mns Digi Round Blk Khaki			1		0	1	0	0	59.99		0	0	59.99	0	0	0	0	0	0	0
7	100	Mr Price Sport Online	6701010406	Mxd Pedometer 2tone Mint 21858			-1		0	-1	0	0	-49.99		0	0	-49.99	0	0	0	0	0	0	0
8	100	Mr Price Sport Online	6701010427	Multi PS-W-MX-23435 Khaki			2		0	2	0	0	19.98		0	0	19.98	0	0	0	0	0	0	0
9	101	BOKSBURG EAST RAND RETAIL PARK	6701010271	Mxd Mns Digital LED watch blk			6		0	6	0	0	779.94		0	0	779.94	0	0	0.02	0	0	0	0
10	101	BOKSBURG EAST RAND RETAIL PARK	6701010392	Mxd LED Square Slim Look Pink			1		0	1	0	0	59.99		0	0	59.99	0	0	0	0	0	0	0
11	101	BOKSBURG EAST RAND RETAIL PARK	6701010472	Mxd MNS Digi Rnd CAMO SS19			3		0	3	0	0	539.97		0	0	539.97	0	0	0.01	0	0	0	0
12	101	BOKSBURG EAST RAND RETAIL PARK	6701010503	Mns Digi Blk Blue 92630 AW20			6	1	1	5	0	16.67	1079.94	179.99	179.99	6	899.95	0	5	0.02	0.06	48.25	75.48	48.23
13	101	BOKSBURG EAST RAND RETAIL PARK	6701010511	Mxd Mns Digital LED watch blk			3		0	3	0	0	389.97		0	0	389.97	0	0	0.01	0	0	0	0
14	101	BOKSBURG EAST RAND RETAIL PARK	6701010517	Mxd BUBBLE Pedometer BLK			4		0	4	0	0	719.96		0	0	719.96	0	0	0.02	0	0	0	0
15	101	BOKSBURG EAST RAND RETAIL PARK	6701010518	Mxd MNS Digi Rnd BLK ORAN S20			4		0	4	0	0	719.96		0	0	719.96	0	0	0.02	0	0	0	0
16	101	BOKSBURG EAST RAND RETAIL PARK	6701010524	Mxd Mns Multi F BLK BLUE S20			1		0	1	0	0	249.99		0	0	249.99	0	0	0.01	0	0	0	0
17	101	BOKSBURG EAST RAND RETAIL PARK	6701010549	MXD KIDS LION SS21 ANA2			1	1	1	0	0	100	99.99	99.99	99.99	1	0	0	0	0	0.03	48.25	41.74	48
18	101	BOKSBURG EAST RAND RETAIL PARK	6701010552	MXD KIDS UNICOR SLIM SS21 LED2			-1		0	-1	0	0	-99.99		0	0	-99.99	0	0	0	0	0	0	0
19	101	BOKSBURG EAST RAND RETAIL PARK	6701010563	MXD PEDOM SAGE SS21 PED2			8	1	1	7	0	12.5	1199.92	149.99	149.99	8	1049.93	0	7	0.03	0.05	47.86	62.61	48
20	101	BOKSBURG EAST RAND RETAIL PARK	6701010564	MXD PEDOM MINT SS21 PED3			0		0			0	0		0	0			0	0	0	0	0	0
21	101	BOKSBURG EAST RAND RETAIL PARK	6701010565	MXD PEDOM PNK SS21 PED3			6		0	6	0	0	899.94		0	0	899.94	0	0	0.02	0	0	0	0
22	101	BOKSBURG EAST RAND RETAIL PARK	6701010567	MXD BUBBLE TURQ SS21 PED2			6		0	6	0	0	1079.94		0	0	1079.94	0	0	0.03	0	0	0	0
23	101	BOKSBURG EAST RAND RETAIL PARK	6701010569	MXD BUBBLE PINK SS21 PED3			2		0	2	0	0	359.98		0	0	359.98	0	0	0.01	0	0	0	0
24	101	BOKSBURG EAST RAND RETAIL PARK	6701010572	MXD LDS MINT RND SS21 DIGI 2			7	2	2	5	0	28.57	1259.93	359.98	359.98	3.5	899.95	0	2.5	0.02	0.12	47.93	150.24	48
25	101	BOKSBURG EAST RAND RETAIL PARK	6701010573	MXD LDS WHITE RND SS21 DIGI 3			0		0			0	0		0	0			0	0	0	0	0	0
26	101	BOKSBURG EAST RAND RETAIL PARK	6701010575	MXD LDS GREEN RND SS21 DIGI 4			0		0			0	0		0	0			0	0	0	0	0	0
27	101	BOKSBURG EAST RAND RETAIL PARK	6701010576	MXD LDS PINK REC SS21 DIGI 5			1	1	1	0	0	100	179.99	179.99	179.99	1	0	0	0	0	0.06	48.25	75.12	48
28	101	BOKSBURG EAST RAND RETAIL PARK	6701010578	MXD LDS GREY RND S SS21 DIGI 6			1		0	1	0	0	179.99		0	0	179.99	0	0	0	0	0	0	0
29	101	BOKSBURG EAST RAND RETAIL PARK	6701010591	MXD MNS RND BLK SS21 MULTI F 2			3		0	3	0	0	749.97		0	0	749.97	0	0	0.02	0	0	0	0
30	101	BOKSBURG EAST RAND RETAIL PARK	6701010592	MXD MNS REC OLI SS21 MULTI F 3			1		0	1	0	0	249.99		0	0	249.99	0	0	0.01	0	0	0	0
31	101	BOKSBURG EAST RAND RETAIL PARK	6701010593	MXD MNS RND GREY SS21 MULTI F4			1		0	1	0	0	249.99		0	0	249.99	0	0	0.01	0	0	0	0
32	101	BOKSBURG EAST RAND RETAIL PARK	6701010596	MXD LDS REC ROSE SS21 MULTI F2			5		0	5	0	0	1399.95		0	0	1399.95	0	0	0.03	0	0	0	0
33	101	BOKSBURG EAST RAND RETAIL PARK	6701010597	MXD MNS RND CAMO SS21 MULTI F1			2		0	2	0	0	559.98		0	0	559.98	0	0	0.01	0	0	0	0
34	101	BOKSBURG EAST RAND RETAIL PARK	6701010598	MXD MNS RND CAMO SS21 MULTI F2			1	1	1	0	0	100	279.99	279.99	279.99	1	0	0	0	0	0.09	47.84	116.87	48
35	101	BOKSBURG EAST RAND RETAIL PARK	6701010599	MXD RND ARRW BLK SS21 MULTI F1			10	1	1	9	0	10	3499.9	349.99	349.99	10	3149.91	0	9	0.08	0.12	48.08	146.08	48
36	101	BOKSBURG EAST RAND RETAIL PARK	6701010600	MXD RND NAVY SS21 MULTI F2			3		0	3	0	0	1049.97		0	0	1049.97	0	0	0.03	0	0	0	0
37	101	BOKSBURG EAST RAND RETAIL PARK	6701010601	MXD REC BLUE SS21 MULTI F PED2			2		0	2	0	0	759.98		0	0	759.98	0	0	0.02	0	0	0	0
38	101	BOKSBURG EAST RAND RETAIL PARK	6701010603	MXD RND CAMO SS21 ANA DIGI			2	1	1	1	0	50	999.98	499.99	499.99	2	499.99	0	1	0.01	0.17	48.02	208.69	48
39	101	BOKSBURG EAST RAND RETAIL PARK	6701010606	MXD KIDS COMBO- BLUES			2		0	2	0	0	319.98		0	0	319.98	0	0	0.01	0	0	0	0
40	101	BOKSBURG EAST RAND RETAIL PARK	6701010609	MXD KIDS LED 1			7	0	0	7	0	0	699.93	0	0	0	699.93	0	0	0.02	0	0	0	0
41	101	BOKSBURG EAST RAND RETAIL PARK	6701010610	MXD KIDS LED 2			11		0	11	0	0	1099.89		0	0	1099.89	0	0	0.03	0	0	0	0
42	101	BOKSBURG EAST RAND RETAIL PARK	6701010612	MXD KIDS LED 3			17	3	3	14	0	17.65	1699.83	299.97	299.97	5.67	1399.86	0	4.67	0.03	0.1	47.86	125.22	48
43	101	BOKSBURG EAST RAND RETAIL PARK	6701010614	MXD DIGITAL LED 3 PINK			5		0	5	0	0	649.95		0	0	649.95	0	0	0.02	0	0	0	0
FT Watches

 

[JohanGduToit]

There are 4 worksheets : "FT Watches', 'FT Sunglasses', 'OD Sunglasses' and 'Sunglass Cases' that must be consolidated into one 'Consolidated' worksheet within the same Excel file. (Without the top 3 rows and only columns A, B, C. D, H and N are applicable).

 

[JohanGduToit]

Here is what I have so far... the Insert of the 'Consolidated' worksheet works; but it creates additional Worksheets 2, 3 and 4 as well. I would like the new worksheet to be created as the 1st worksheet. (the commented code does not want to work).

Struggling to copy/consolidate the 4x worksheet's data to/on the 'Consolidated' worksheet.

Please advise...

[CODE=vba]Public Sub FormatMrPSportTEST02(sFile As String)

'On Error GoTo Err_FormatMrPSport
    
    Dim xlApp As Object
    Dim xlSheet As Object
    
    Application.SetOption "Show Status Bar", True
    vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting Mr P Sports File... Please wait.")

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)

    With xlApp
        .Application.ScreenUpdating = False
        .Application.DisplayAlerts = False
        
        .Application.Sheets(Array("FT Watches", "FT Sunglasses", "OD Sunglasses", "Sunglass Cases")).Select
        .Application.Rows("1:3").Select
        .Application.Selection.Delete shift:=xlUp
        .Application.Range("E:E,F:F,G:G,I:I,J:J,K:K,L:L,M:M,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,V:V,W:W,X:X").Select
        .Application.Selection.Delete shift:=xlToLeft
        .Application.Sheets.Add.Name = "CONSOLIDATED"
        '.Application.Sheets.Add(Before:=Sheets("FT Watches")).Name = "CONSOLIDATED"   'THIS LINE AND THE ONE BELOW DOES NOT WORK
        '.Application.Sheets.Add(Before:=Sheets(1)).Name = "CONSOLIDATED"       
        
        '.Application.Sheets("FT Watches").Select     'UNABLE TO SELECT ALL CELLS CONTAINING DATA, THE LINE CONTAINING "xlLastCell " FAILS
        '.Application.Range("A1").Select
        '.Application.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
        '.Application.Selection.Copy
        '.Application.Sheets("CONSOLIDATED").Select
        '.Application.Range("A1").Select
        '.Application.ActiveSheet.Paste
                
        .Application.Range("A1").Select
        .Application.ActiveWorkbook.Save
        .Application.ActiveWorkbook.Close
        .Application.DisplayAlerts = True
        .Quit
   
        .Application.ScreenUpdating = False
        .Application.DisplayAlerts = False
   End With
   
   vStatusBar = SysCmd(acSysCmdClearStatus)

   Set xlSheet = Nothing
   Set xlApp = Nothing

    
'Exit_FormatMrPSportTEST:
'    Exit Sub
    
'Err_FormatMrPSportTEST:
'    vStatusBar = SysCmd(acSysCmdClearStatus)
'    MsgBox Err.Number & " - " & Err.Description
'    Set xlSheet = Nothing
'    Set xlApp = Nothing
'    Resume Exit_FormatMrPSportTEST
    
End Sub

[/CODE]

 

[JohanGduToit]

I'm sure there must be an easier way to do this; maybe looping through the worksheets (Sheet(1) to CountSheets) and append each sheet's data (Range A1 to LastRow/Columns A to F) to the 'Consolidated' worksheet? Just don't know how to code that...

 

[Joe4]

Quite frankly, I would not approach it that way.
I would create the Macros/VBA to manipulate the Excel data right in Excel (I find that much easier than trying to do it through Access VBA).
Then I would simply call that Excel VBA code from Access VBA.

 

[JohanGduToit]

Hi Joe,

Unfortunately I have to do if from Access... we receive a new file from the customer once a week and I am trying to automate the import process. I can not expect the user to copy and paste a macro into the new sheet every time. But thank you for your feedback...appreciated!

 

[Joe4]

Hi Joe,

Unfortunately I have to do if from Access... we receive a new file from the customer once a week and I am trying to automate the import process. I can not expect the user to copy and paste a macro into the new sheet every time. But thank you for your feedback...appreciated!

You don't have to. You can create a separate Excel file that has those macros and call that from your VBA code.
If you are trying to run them from Access, they are already running from code that is not in the actual data files.
This would just be storing them in Excel instead of Access.

The advantage to developing them in Excel is that you have the benfit of all the debugging tools, and it is a lot easier to develop and test.

 

[Micron]

See if this site helps you.

EXCEL Import And Export

You're going to have to loop over the sheets in question and copy/paste where required. I don't understand the comment about having debugging tools in Excel. The vb editor is the same, regardless whether you have Access open or Excel. As long as you have set the correct references, you can even run Excel-specific functions from Access. Can't say if there is a limited availability or not, just that I've done that but can't recall what functions I used.

 

[Joe4]

See if this site helps you.

EXCEL Import And Export

You're going to have to loop over the sheets in question and copy/paste where required. I don't understand the comment about having debugging tools in Excel. The vb editor is the same, regardless whether you have Access open or Excel. As long as you have set the correct references, you can even run Excel-specific functions from Access. Can't say if there is a limited availability or not, just that I've done that but can't recall what functions I used.

Can you step through the VBA code line-by-line and watch what happens on the Excel side while it is happening?
(Maybe you can, I have never tried it)?

You certainly cannot take advantage of Excel's Macro Recorder using Access, though.