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!!

 

[Micron]

As long as the workbook isn't invisible and you have no issues regarding your screen image updating or haven't turned anything off, sure - you can see the effects.
Re: the latter, if you mean record what's going on with the Access window from Excel, I'd guess no. Could you launch the recorder from Access using ribbon commands? Don't know and it wouldn't make sense to even try for anything more than curiosity, because you'd certainly need to interact with the Excel app, which means you might as well just use the recorder from Excel.

 

[Joe4]

I meant using the Excel Macro Recorder from Excel.
I maintain that it is easier to develop Excel VBA code from Excel, than it is from Access, especially if you are not an expert VBA coder.
That's all I am saying.

 

[JohanGduToit]

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.

Morning 'Micron'.

Thank you!! The subsequent link "Import Data from All Workbooks in a Single Excel File into One Table" from the link you sent me was exactly what I was looking for. I literally copied the code and made 1 or 2 changes and it worked like a charm!! I am so happy! The link contain so much info which I will definitely refer to in future. Once again, thank you very much!

 

[JohanGduToit]

I meant using the Excel Macro Recorder from Excel.
I maintain that it is easier to develop Excel VBA code from Excel, than it is from Access, especially if you are not an expert VBA coder.
That's all I am saying.

Morning 'Joe',

I'm familiar with the Macro Recorder and use it frequently to 'build' Excel formatting code; but it becomes tricky when you use late binding (I think that's the correct term) in which case you cannot simply apply the Macro VBA directly in Access. However, thank you so much for your time and effort to assist me, much appreciated indeed.

 

[JohanGduToit]

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.

Joe, I was not aware that you can create a seperate Excel file containing macros which can be called from my Access VBA to format another Excel file. How would one go about doing that?

 

[Micron]

You're welcome. Likely many of the questions you're going to continue to have are readily answered with a bit of searching. I"m saying that because the answers can be a bit involved as you can see by the first link I gave you. How to run external code is in the same league, I think. Search for "run excel macro from access" perhaps.

Run Excel Macro from Access VBA

This Access tutorial explains how to run Excel Macro from Access VBA, either write Excel Macro directly in Excel or you can write Excel Macro in Access.

access-excel.tips