Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel open an existing MS Query in edit mode via macro attached to a button.
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro attached to command button - VBA form | Excel Discussion (Misc queries) | |||
How can I edit my macro button images in Excel 2007. | Excel Discussion (Misc queries) | |||
I know you can't run a macro while in edit mode, but... | Excel Discussion (Misc queries) | |||
why does edit query button not open query | Excel Discussion (Misc queries) | |||
running macro while in the edit mode | Excel Programming |