I would usually do this in access using reports but I have been task
with developing some reports in excel that pulls data from an access
database. The database has about 6000 records (yea I know don't even
go there) but the reports pull only a fraction of the records into
each reports. There are five distinct reports and each report will
have six distinct criteria (area field) so that 30 reports will be
produced in total. The data will need to be refreshed from time to
time and the reports reprinted or the data look at in excel.

The reports will also be reused on new future projects where the
database will be located in a different directory on a network drive,
under a different name, and even on a different network with different
mapping setup. The users will not know how to do anything more but
open the reports, press the query button to refresh the data, and
print the report.

My plan is to setup these reports and setup a data source for each
using a Microsoft Query with criteria setup for each report (30 data
sources / queries). I could do all or some of it in VBA if necessary
but I am not a heavy duty VBA programmer. I have done some and
understand basic concepts and can take general code and adapt it for a
specific purpose. Is there a way to replace the path and file name in
the SQL query string based upon the contents of a worksheet cell so I
don't have to edit it manually each time. Do I have to edit more than
the SQL query string and if so where and how? Is there a way to use
the contents of an excel worksheet cell to replace the criteria of the
report so it doesn't have to be manually entered. If I could do this,
then not only would I have to manually edit 30 reports but my users
could do the set up for future projects by typing in the path and file
name and the criteria into cells and could hit a setup button to run a
VBA script or macro. Sorry if this has been answered before but I have
searched and could not find it. But at least I tried!

Is there a better approach than what I'm thinking of that will meet
the requirements. The reports are REQUIRED to be in EXCEL. No
exceptions. I've already lost that one.