none
Excel VBA variables - pass thru to MS Query?

    Question

  • 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.

    Monday, June 18, 2012 7:34 PM

All replies

  • 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
    

    Monday, June 18, 2012 8:39 PM
  • An Excel InputBox is what I used in a macro:

    Sub ReportingPrompts()

    CreateObject("WScript.Shell").Popup "Switch to the DATA tab, & click on the REFRESH ALL icon to retrieve data.", _
            10, "INSTRUCTIONS"
    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", _
                        Default:="MM/DD/YYYY")

        On Error GoTo 0
        Application.DisplayAlerts = True

            If EntityNum = 0 Then
                Exit Sub
            Else
                '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
            End If
    End Sub

    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)')

    UNION ALL

    --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.

    Monday, June 18, 2012 11:19 PM
  • 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.

    Tuesday, June 19, 2012 12:56 AM
  • 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.

    Wednesday, June 20, 2012 12:29 PM