Great - thanks guys! Is it also possible run a module
or/and macro that is created in Access directly from Excel?
I have a macro that runs a series of queries and modules
that returns an excel file as output. Is there any way
that I could run this procedure directly from Excel and
return the output to Excel without the user ever seeing
the Access portion.
The parameters for the Access model are going to be passed
in through entry boxes in Excel.
Thanks again!
-----Original Message-----
You could create a 'stored procedure' in the database.
Below is an
example (depending on permissions, you may actually get
away with
executing this DDL from MS Query's SQL window!):
CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
RefID,
DateEffective,
Earnings
FROM
EarningsHistory
WHERE
DateEffective
BETWEEN start_date AND end_date;
For MS Query, here's what should appear in the SQL window
to run the
above procedure with parameters:
{Call MyStoredProc('01 JAN 2001', '01 JAN 2004')}
--
"Dick Kusleika"
wrote in message
...
Rosson
You can create an External Data table in Excel that
uses a parameter query.
Under the Data menu, choose Get External Data - New
Database Query.
Choose Data Source - Select Access from the listbox and
navigate to and
select the database in question. OK.
Choose Columns - select the table or query and the
fields that you want to
include. Next.
Filter Data - do nothing here. Next.
Sort Order - select a field on which you want to sort,
or leave blank to use
the order inherent in the table or query. Next.
Finish - select View Data in MSQuery and click Finish.
In MSQuery: Under the View menu, select Criteria. In
the criteria pane,
select a field on which you want to filter. Let's say
you select two
fields, State and Products. In the Value row, type
[Enter State]
under the State field and
[Enter Product]
under the Products field. When you use the brackets,
it's interpreted as a
prompt string - this is what makes it a parameter query.
Under the File menu, choose Return Data to Microsoft
Excel. It will now
prompt you to input values for each parameter that you
entered. For the
first prompt, type CA. For the second, type DVD.
Back in Excel, it will ask you where to put the data.
Click Ok or choose a
different cell and click OK.
Now you should have an External Data table in Excel
that shows all records
where the state is CA and the Products is DVD. The
External Data Toolbar
should also be showing. Every time you hit the Refresh
button on this
toolbar, it will prompt you to enter values for your
parameters.
Finally, there is a Parameters button on the toolbar.
Click it and look at
the options you have for customizing you parameters.
For instance, you can
have the parameter get its value from a cell instead of
prompting you.
If you want to re-edit the query, it will not run you
through that wizard
again, but will tell you that you have to edit it in
MSQuery. The wizard
can't handle parameter queries.
If you have any questions, post back.
--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.
"Rosson Cain"
wrote in message
...
Hello
I would like to be able to create a form (or some
sort of
interface) in Excel that would allow the user to
select
certain perameters, then send these parameters to
Access
and run a select query on a pre-existing table using
the
selected parameters. I would then like the query
results
sent back to Excel and displayed in a (preferably the
same) form that would allow the Excel user to interact
with the results. (For example, select a record store
that
meets the criteria of being in CA and selling DVDs as
well.)
Any help is appreciated. Thanks.
.