I am using Excel 2007 as a front end to retrieve data from a SQL2005 back end database. I've pasted the SQL query into Microsoft Query, which works fine for returning fixed result sets. I would like to be able to prompt the end user from an Excel dialog box for two key fields, which would then be passed on as variables to Microsoft Query. A parameter query wouldn't work with Microsoft Query because of its complexity - the SQL statement is a CTE query with multiple joins.
I'd like to find if there is a method to save an InputBox entry from Excel as a variable, and use that variable in Microsoft query. Setup of an ODBC connection string to process SQL commands appears to be a much more involved approach, as my VBA experience has been manipulating data once it has been returned to Excel.
Is this what you mean?
Sub SaveFields() Dim field1 As String Dim field2 As String Dim tempStr As String Do tempStr = InputBox("Enter field1 name") field1 = tempStr Loop While (Len(tempStr) = 0) Do tempStr = InputBox("Enter field2 name") field2 = tempStr Loop While (Len(tempStr) = 0) End Sub
An Excel InputBox is what I used in a macro:
CreateObject("WScript.Shell").Popup "Switch to the DATA tab, & click on the REFRESH ALL icon to retrieve data.", _
Dim EntityNum As Long, AsOfDate As Date
On Error Resume Next
Application.DisplayAlerts = False
EntityNum = Application.InputBox _
(Prompt:="Please enter starting entity number.", _
Title:="TOP LEVEL ENTITY", Type:=1)
AsOfDate = Application.InputBox _
(Prompt:="Please enter reporting date.", _
Title:="REPORT AS OF DATE", _
On Error GoTo 0
Application.DisplayAlerts = True
If EntityNum = 0 Then
'MsgBox "Entity Number: " & EntityNum
Range("A1") = "Starting Entity:"
Range("c1").Value = EntityNum
'MsgBox "As of: " & Format(AsOfDate, "mm/dd/yyyy")
Range("A2") = "As of:"
Range("c2").Value = AsOfDate
I'd like to pass the values for EntityNum in cell C1 and AsOfDate in cell C2 to the SQL statement that I saved in Microsoft Query:
WITH Structure (Name,ChildID,ParentName,ParentID,… - other fields)
as (SELECT T1.Name,T1.ID,T2.Name,T3.OwnerID, … other fields)
FROM Table3 T3 join Table1 T1 on T3.CompanyID=T1.ChildID
Join Table2 T2 on T3.ParentID=T2.ChildID
Join Company C on T3.CompanyID=C.CompanyID
JOIN Userfield U1 on … - additional inner joins)
WHERE T3.OwnerID = '(Entity Numeric value to go here)'
and( convert(char,T3.Startdate,101) <= ' (Date value to go here)')
and (T3.EndDate is null or convert(char,T3.Enddate,101) >
'(same Date value to go here)')
--Recursive Query follows here
I don't know if I can declare a variable in Excel VBA and get it to pass through to Microsoft Query.
For the date, an InputBox doesn't offer much error checking. You might want to look into a Calendar Control on a UserForm so you get a valid date. http://www.fontstuff.com/vba/vbatut07.htm
I don't see why you can't do something like WHERE T3.OwnerID = variable1 where variable1 = InputBox("enter ID"). I do that on my website where I have a form where I input information and that information is saved to a variable.
I'm not finding any syntax that would work with Microsoft Query and Excel. The MS query window returns an error that "'inputbox' is not a recognized built-in function name". Since MS Query runs the command against a SQL DB, it needs to be a valid SQL statement, which doesn't include Excel functions.
I haven't found any examples of a simple method using VBA to prompt a user for a variable from Excel, then pass that variable to a SQL statement in MS Query to be executed, with the results returned to Excel. A Parameter query was the only example I've found, and that doesn't appear to be an option given the complexity of the SQL statement being run.