ExcelBanter - Excel open an existing MS Query in edit mode via macro attached to a button.

How about modifying the WHERE clause of the query string of the QueryTable
directly. I assume you can get the desired date information from the user
via an InputBox or a UserForm and then use that information to build a new
query string. See the code below, which is an example using QueryTables (MS
Query) with an external Excel file.

Troy

Sub Test1()

Dim sSQL As String
Dim sWhere As String

'''Display the current query string.
MsgBox Sheet1.QueryTables(1).Sql

'''This is the static portion of the query string that doesn't change.
'''(Example: Query a named range: nData in an Excel file:
DataSource1.xls).
sSQL = "SELECT * FROM `C:\My Documents\DataSource1`.nData nData"

'''Define the new query string (put a space between the 2 strings).
sWhere = "WHERE (nData.myCol1 1.2)" '''<===== Modify this string
sSQL = sSQL & " " & sWhere

'''Set the Query Table Sql property to the new query string.
Worksheets(1).QueryTables(1).Sql = sSQL

'''Display the new query string.
MsgBox Sheet1.QueryTables(1).Sql

'''Refresh the Query Table data on the worksheet.
Worksheets(1).QueryTables(1).Refresh

End Sub

"systems analyst" wrote in message
om...
I have an Excel spreadsheet with an MS Query on it. The end user
wants to click on a button that will run an attached macro in VBA that
opens the query it edit mode so he can change the date range in the
criteria. The macro does some other work also. I cannot use
parameters or values in cells on the spreadsheet because of a
disagreement in the date/time formatting between Excel and the ODBC
database the query uses. I searched the google groups for a half hour
and tried the VBA & Excel help files but could not find it.

Thanks for any help!