Paste Contents from Clipboard to MS Query Parameter

CorrieAnn

New Member
Joined
Sep 4, 2007
Messages
17
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.

Parameter Value.png


Can someone please provide some code for me?

Thanks!
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,612
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

New Member
Joined
Sep 4, 2007
Messages
17
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

New Member
Joined
Sep 4, 2007
Messages
17
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,612

ADVERTISEMENT

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.
VBA Code:
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

New Member
Joined
Sep 4, 2007
Messages
17
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:


Run-Time Error.png


It errored on the line I commented.
VBA Code:
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,612
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.
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,529
Messages
5,596,695
Members
414,088
Latest member
rodriboraun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top