I currently use Excel in conjunction with Microsoft Query to extract
data from a database and run certain reports. Most of these reports
must be based on a date range, or some other similar criteria. My
problem is that when I want to update the report for a new date range
or the like, I have to hit "edit query" to open up Microsoft Query,
then edit the criteria. I would like to know if there is a way to
update the parameters in the query without manually opening MS Query
and editing. I tried to create a macro that would do it for me without
any luck. Optimally, what I would like is either a macro that pops up
an input box for the user to enter new parameter(s), or I could have a
cell on the spreadsheet where I would enter a new parameter, then the
SQL code would take this cell's data and put it into the query for me.
This would make the reports much more user friendly to edit. Any ideas?


---
Message posted from http://www.ExcelForum.com/

You seem to be describing a stored procedure <g. What database are you using?

--

smunie wrote in message ...
I currently use Excel in conjunction with Microsoft Query to extract
data from a database and run certain reports. Most of these reports
must be based on a date range, or some other similar criteria. My
problem is that when I want to update the report for a new date range
or the like, I have to hit "edit query" to open up Microsoft Query,
then edit the criteria. I would like to know if there is a way to
update the parameters in the query without manually opening MS Query
and editing. I tried to create a macro that would do it for me without
any luck. Optimally, what I would like is either a macro that pops up
an input box for the user to enter new parameter(s), or I could have a
cell on the spreadsheet where I would enter a new parameter, then the
SQL code would take this cell's data and put it into the query for me.
This would make the reports much more user friendly to edit. Any ideas?


---
Message posted from http://www.ExcelForum.com/

If you are not directly using the MS Query output in a PivotTable, you
can do the following:

[I just went through the steps below with Access 2002 and XL2002 to
ensure I got them correct.]

In Excel, enter a start date in some cell. Similarly, enter a end date
in another cell.

In MS Query, I assume you have criteria set up such as =#1/1/2003# and
<=#06/30/2003#.

Instead, replace the specific dates with =[enter start date] and <=
[enter end date]

Now, when you run the query (or exit MS Query), you'll be asked for the
start and end dates. Specify appropriate dates.

Back in XL, the dialog box will have a Parameters... button. Click it.
In the resulting dialog box, for each of the 2 parameters specify the
cell you selected in the first step above. Optionally, select the
checkbox to run the query whenever the cell values change.

That's it. You are done.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I currently use Excel in conjunction with Microsoft Query to extract
data from a database and run certain reports. Most of these reports
must be based on a date range, or some other similar criteria. My
problem is that when I want to update the report for a new date range
or the like, I have to hit "edit query" to open up Microsoft Query,
then edit the criteria. I would like to know if there is a way to
update the parameters in the query without manually opening MS Query
and editing. I tried to create a macro that would do it for me without
any luck. Optimally, what I would like is either a macro that pops up
an input box for the user to enter new parameter(s), or I could have a
cell on the spreadsheet where I would enter a new parameter, then the
SQL code would take this cell's data and put it into the query for me.
This would make the reports much more user friendly to edit. Any ideas?


---
Message posted from http://www.ExcelForum.com/

Here is some sample code that I use to do the sort of thing that you
require. The sql is contained in cell A1, and the query returns data
starting in cell A4. The code assumes that there is no pre-existing
querytable on the active sheet.
I use a formula in cell A1 to build the complete sql string from
information in a number of other cells, so that incorporation of
parameter values is easy.

Sub GetData()
Dim QSh As Worksheet, ConStr As String, Sql As String

Set QSh = ActiveSheet
ConStr = "xxxx;DSN=xxx;UID=xxxx;PWD=xxxx;SERVER=xxxx;"
QSh.QueryTables.Add Connection:=ConStr, _
Destination:=QSh.Range("a4")
With QSh.QueryTables(1)
.CommandText = QSh.Range("a1")
.Refresh
.Delete
End With
End Sub

Thanks to everyone for their input! With my limited programing
knowledge, I decided to go Tushar's suggestion. This is a great new toy
for me to experiment with! Thank you so much Tushar!!


---
Message posted from http://www.ExcelForum.com/

You are welcome. Glad to be of help.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Thanks to everyone for their input! With my limited programing
knowledge, I decided to go Tushar's suggestion. This is a great new toy
for me to experiment with! Thank you so much Tushar!!


---
Message posted from http://www.ExcelForum.com/