MS Query - change parameter with Excel cell

E

emea training 2

I have an Excel file which links to our database via ODBC and Microsof
Query.

The Excel file contains a graph of sales figures which I have set t
refresh every 5 mins without user intervention. It is then permanentl
displayed on a projector.

However, every day we have to edit the Query to change the date to b
= the current date.
Click to expand...

Can I link the Query to a cell in Excel which contains today's date a
this would be much simpler?

Thanks
 
T

TedGrier

I have the exact same problem, and have been posting the question daily.
PLEASE share the answer with me if you find it. [email protected]
See my thread this morning....

Ted Grier
 
D

Debra Dalgleish

In an empty cell on the worksheet, type: =TODAY()
Choose Data>Import External Data>Edit Query
Remove the date in the query, and enter a parameter. For example:
[What Date?]
Choose File>Return data to Microsoft Excel
When prompted, type the current date

Select a cell in the query results
Click the Query Parameters button
Choose 'Get the value from the following cell'
Click in the reference box, then select the cell that contains
the TODAY formula.
Check the 'Refresh automatically' box
Click OK
 
E

emea training 2

Many thanks - this works for my queries that return data to Excel.

I also have a query that populates a pivot table - so that I can get i
to refresh every 2 mins - but this does not alllow this fix. It com
up with the message "Parameters are not allowed for this query".

Is there any way around this
 
Top