Passing data from MSQuery to VBA?

[Notinkeys]

To be clear, I am NOT a VBA programmer. I'm doing good to be able to pronounce it, but a task I have requires me to get a piece of VBA code to work with a query and I am missing one element- how to pass a variable back from a query criteria to my VBA so the SQL can select on it.

So far, I'm at a loss. When I refresh the sheet, it asks for the value ([Value=?]) and uses it in the query to select via SQL. When the user double-clicks, it runs VBA code, including more SQL, to retrieve the data from an outside source via ODBC, and populates another sheet in the workbook. What I am needing to do is get that value in the query and feed the VBA with it. Is this possible?

Thanks!
NotInKeys

 

[John_w]

It sounds like you need VBA code which uses the QueryTable CommandText property and/or Parameters collection. CommandText is the SQL Select string run by the query and can be modified by VBA as required. Run this code to display the queries and parameters on the active sheet.

Public Sub Display_Queries()

    Dim qt As QueryTable
    Dim qp As Parameter
    
    For Each qt In ActiveSheet.QueryTables
    
        MsgBox "Query Name = " & qt.Name & vbNewLine & _
                "Destination = " & qt.Destination.Address & vbNewLine & _
                "CommandText = " & qt.CommandText
                
        For Each qp In qt.Parameters
            If qp.Type = xlPrompt Then
                MsgBox "Parameter Type = xlPrompt" & vbNewLine & _
                    "Name = " & qp.Name & vbNewLine & _
                    "PromptString = " & qp.PromptString
            ElseIf qp.Type = xlRange Then
                MsgBox "Parameter Type = xlRange" & vbNewLine & _
                    "Name = " & qp.Name & vbNewLine & _
                    "SourceRange = " & qp.SourceRange.Parent.Name & "!" & qp.SourceRange.Address
            ElseIf qp.Type = xlConstant Then
                MsgBox "Parameter Type = xlConstant" & vbNewLine & _
                    "Name = " & qp.Name & vbNewLine & _
                    "Value = " & qp.Value
            End If
        Next
        
    Next

End Sub

The user double-clicking the sheet is probably triggering the Worksheet_BeforeDoubleClick event. View the relevant sheet module in the VBA editor to see the code, which starts:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

 

[Notinkeys]

Thanks a bunch, John! I will try this out and see how it works.