Replacing Path/filename and criteria in MS Query based on cell contents using VBA

[dollinger64]

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.

 

[Mark J]

1. create a odbc connection to the access database
2. use excel pivot tables to create your reports
3. create individual or wildcard queries per report
....this method is much faster and requires no maintenance on your part, they
can be setup to refresh data on open and changing fields can be done on the
fly, i use this method to create reports against multiple sql servers and it
is the prefered method....access is really not the way to go for data storage
or report development.

 

[dollinger64]

1. create a odbc connection to the access database
2. use excel pivot tables to create your reports
3. create individual or wildcard queries per report
...this method is much faster and requires no maintenance on your part, they
can be setup to refresh data on open and changing fields can be done on the
fly, i use this method to create reports against multiple sql servers and it
is the prefered method....access is really not the way to go for data storage
or report development.

Mark,

Does this allow the database path to also be changed without editing
it manually in each query or rebuilding the quries. I agree but I
didn't create the database and don't have any control over it. I am
not a very experienced excel user. Is there a pivot table tutorial
somehwere? Thanks!

 

[Dick Kusleika]

Start here:

http://www.dicks-clicks.com/excel/ExternalData.htm

Depending on how you've set up the queries, you'll need to change the path
in both the CommandText property AND the Connection property of the
QueryTable object. In most cases, there's no need for the path to be in the
SQL (CommandText) but if you create the query through the UI, it will be
there. Specifically see the 'Change the database location' link on that
page.

For your specific application, you'll want to use the Worksheet_Change event
to determine when the user has entered a new path. Instead of having them
enter the path into a cell, you might provide a command button that can be
clicked and allow the user to navigate to the proper file. For that, you
would use the GetOpenFileName method. None of this is discussed on that
site, so be sure to follow up here with questions if you have them.

For dynamic critieria, be sure to read the 'Parameters' link on the above
site. The built-in Parameters feature will handle most cases. In those
cases that it doesn't, you can programmaticallly change the CommandText's
WHERE clause just like you do for the path to the database.

Hopefully that gets you started. Post back for follow-up questions and
clarifications.

 

[Emma Hope]

Dollinger,

I am an inexperienced user like yourself, however i have the same problem as
you i found that DAO works better with this than MS Query and ODBC (look it
up on the MS knowledge base), it took me a little while to follow it, but it
works so much better, you can change the path etc, i have it set up so it
find the database in the same folder as the spreadsheet using 'strPath =
ThisWorkbook.Path & "\Member Database.mdb"' etc. You can even pass parameters
back to Access etc. I haven't figured out the SQL it needs to query as well
as MS Query, so personally i just make the query how i want it in Access and
then pull that through using DAO.

HTH.
Emma