This would allow me to use both the sheets as one table and fetch all of my data into a new sheet.
Click here to download the demo file & use it to understand this technique. If yes please put in the comment below how do you use the same and what has been your experience. If you are new to VBA, Excel macros, go thru these links to learn more.
We need to add a reference to the Microsoft Active X Data Objects Library to be able to use the worksheet as a database table. I usually select the most recent version, however if you are developing a product it will be best suited if you are familiar with the operating system and office version used by the end-user’s system and accordingly select the best version available.
As I have suggested earlier, one entire sheet will be treated as one single table, so if you have multiple datasets that were currently organized within one sheet you may have to create multiple sheets to store that data to be able to use them as tables. Many people ask, how to consolidate 2 or more sheets which have the similar data.
Well I would have adopted this method and wrote a simple query as below.
Once this is done we need to hit the road with some VBA code.
On this line, we define all the possible file extensions that we are allowed to create an Excel Workbook and then use as our database.
Comment - Just me sounding off Mailbag - Readers' problems solved Articles - Bits and pieces Links - Check out these sites About - Who is Martin Green anyway?