Paste Contents from Clipboard to MS Query Parameter

[CorrieAnn]

I have a spreadsheet with multiple queries linked to it. When the user wishes to refresh the queries, they press a "refresh all" button I have provided to them which just saves them the time of having to utilize the built-in menu options simply because they never remember where to find it. The code behind the button is simple: "ActiveWorkbook.RefreshAll"

However, one of the queries it is refreshing requires a parameter (assembly number). Right now they have to copy this value from cell A15 and paste it into the popup window when prompted. I would like the macro to grab the contents from cell A15 and paste it into the popup window for them. It will always appear in cell A15. I can't seem to find anything on how to pass a value from the clipboard to a query parameter when prompted.

Can someone please provide some code for me?

Thanks!

 

[John_w]

You shouldn't need any code, nor should you need to copy and paste the cell value into the 'Enter Parameter Value' dialogue.

Simply click the up arrow, click cell A15, which enters A15 as Parameter1 and tick 'Use this value/reference for future refreshes'. After this the dialogue shouldn't appear when you refresh the query and it will always use the value in A15.

 

[CorrieAnn]

Ok, I did that and it worked. But I needed to take it off since it broke another part of the template. For those needed to know how:

Data -> Connections -> Properties -> Definition Tab -> Parameters -> Click "Prompt for value using the following string"

 

[CorrieAnn]

Let me explain a little more:

I have two buttons on the quoting tool (excel spreadsheet form) that use the same line of code: ActiveWorkbook.RefreshAll.

Button one is called "New Quote" and is pressed by the user when initiating a new quote. The user must enter the assembly number as a parameter value because up to this point, the assembly number is not known and cannot be referenced anywhere on the spreadsheet. Once the assembly number is entered, all queries are run (refreshed) and the associated data populates the spreadsheet.

Button two is called "Refresh All" and is pressed by the user when updated pricing information is entered into our main database by our purchaser. This data needs to be pulled through to the the quoting tool later in the process, which happens by refreshing the queries. It is at this point where I would like the one query requiring the parameter to refer to the cell value in A15. I cannot use the 'Use this value/reference for future refreshes' check box because it impacts the "New Quote" button.

Suggestions?

 

[John_w]

Try this macro for the "New Quote" button. You must change the name of the table in the code to the actual name of the table for the "New Quote" MS Query. This name can be seen in the Table Tools Design tab, and Connection Properties -> Used in tab.

Public Sub New_Quote_Button()

    Dim table As ListObject
    Dim param As Parameter
    Dim paramValue As String
    
    Set table = ActiveSheet.ListObjects("Name_of_table")  'CHANGE THIS - NAME OF TABLE
    Set param = table.queryTable.Parameters(1)
    
    If param.Type = xlRange Then
        If param.SourceRange.Value = "" Then
            paramValue = InputBox("Enter Assembly Number")
            If paramValue <> "" Then
                ActiveSheet.Range("A15").Value = paramValue
                ActiveWorkbook.RefreshAll
            End If
        Else
            ActiveWorkbook.RefreshAll
        End If
    End If
        
End Sub

 

[CorrieAnn]

Here are the steps I took:

• Inserted your code into my existing module
• Made the needed adjustment for the table name (keep in mind I inherited this project and I was not the one who named the tables)
• Assigned the macro to my "New Quote" button
• Pressed the "New Quote" button

This is the error I received:

It errored on the line I commented.

Public Sub New_Quote_Button()

    Dim table As ListObject
    Dim param As Parameter
    Dim paramValue As String
   
    Set table = ActiveSheet.ListObjects("Table_Query_from_Visual_7_1")  'CHANGE THIS - NAME OF TABLE
    Set param = table.QueryTable.Parameters(1)
   
    If param.Type = xlRange Then  '<---- THIS IS THE LINE THAT CAUSED THE ERROR *********
        If param.SourceRange.Value = "" Then
            paramValue = InputBox("Enter Assembly Number")
            If paramValue <> "" Then
                ActiveSheet.Range("A15").Value = paramValue
                ActiveWorkbook.RefreshAll
            End If
        Else
            ActiveWorkbook.RefreshAll
        End If
    End If
       
End Sub

 

[John_w]

It seems like you haven't added a parameter to the query. Try this macro which adds the Assembly Number parameter if it doesn't exist, then reads its value from A15, prompting if the cell is empty.

Public Sub New_Quote_Button()

    Dim table As ListObject
    Dim param As Parameter
    Dim paramValue As String
    Dim paramType As Long
   
    Set table = ActiveSheet.ListObjects("Name_of_table")  'CHANGE THIS - NAME OF TABLE
    Set param = table.queryTable.Parameters(1)
   
    'Only way of determining if the parameter exists is to see if an error occurs when reading any of its properties
   
    On Error Resume Next
    paramType = param.Type
    If Err.Number > 0 Then
        On Error GoTo 0
        Set param = table.queryTable.Parameters.Add("Assembly Number", xlParamTypeVarChar)
        param.SetParam xlRange, ActiveSheet.Range("A15")
        param.RefreshOnChange = False
    End If
    On Error GoTo 0
   
    If param.Type = xlRange Then
        If param.SourceRange.Value = "" Then
            paramValue = InputBox("Enter Assembly Number")
            If paramValue <> "" Then
                ActiveSheet.Range("A15").Value = paramValue
                ActiveWorkbook.RefreshAll
            End If
        Else
            ActiveWorkbook.RefreshAll
        End If
    End If
       
End Sub

You can rename the table in the UI and use the same name in the code, if you wish.