Pulling data from external database based on criteria using VBA

I am attempting to build a VBA macro that will grab data from an external database located on my network. The database to be pulled from will be 1 model with various tabs that will be added to as new monthly information comes in (I might set this portion up in Access given the breadth of data).

Regardless, the data will be segregated by month. So, for example, there will be a tab titled "May 2012" and I'll need to tell my model which month (i.e. May 2012) to pull data from the outside database's tab. This data will then populate within my model in a given sheet, lets call this sheet "Data pulled from outside database".

The easy part of this is that the various monthly tabs have the same columns and will not change from month to month (there are only about 6 or 7 columns) but 1000's of rows that will change.

Another nice feature would be to clear the "data pulled from outside database" tab each time the VBA is ran such that if June 2012's monthly data is less than May, it wont mix the two.

If anyone could help I'd greatly appreciate it. I like to use defined names and i think this is easier for VBA to handle (neater) so feel free to explain using names.

Also if you want to explain a solution that pulls from an Access file and/or excel that'd be great!

Thanks!

The code below shows one way to open Access from Excel 2003
and run a query, then copy the results into a worksheet.

Please Login or Register  to view this content.