none
How to set a cell value using the result of a parameterized SQL query? RRS feed

  • Question

  • I have a worksheet with columns A, B and C. Then I need to run a query on my SQL Server using the values from columns A and B to find the value that should be placed on column C.

    I've created the query like this:

    SELECT * FROM Tablename

    Then added the filtering steps considering two parameters I've created on Query Editor.

    Now, how can I connect this existing worksheet to the query, passing the cells values as parameters to get the result (single row)?


    Juliano Nunes - http://linkedin.com/in/julianonunes

    Lembre-se de clicar em "Votar como útil" e "Marcar como Resposta" caso tenha respondido sua dúvida.

    Remember to "Vote as Helpful" and "Mark as Answer" if your question has been answered.

    Tuesday, January 23, 2018 12:03 PM

All replies

  • Hi,

    Based on your description, I will move your thread to Excel for Developer forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.


    Wednesday, January 24, 2018 7:53 AM
  • Hello Juliano,

    Do you mean that you have got all data from a Table? So, now you want to filter on the data with A and B to get C and then put C in column C?

    Did you have any code now? Which type connection are you using?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 25, 2018 5:15 AM
  • Hello,

    I didn't import all the table data using the connection I made to the SQL Server, I've just created the parameterized query using the editor.

    If I import all of the data from the SQL Server table I could use the vlookup probably, however this database table has hundreds of thousands row and I only need to find the value for aproximately 50 of these rows.

    My worksheet is like this:

    Column A | Column B | Column C
       1     |  223     |    ?
       2     |  545     |    ?
       8     |  542     |    ?   
    Then my database table has all of these 3 columns (along with several others that are not important), I need compare columns A and B from the worksheet with the equivalent columns A and B in the database table, to return column C value from the database (without importing the entire table into a new worksheet).


    Juliano Nunes - http://linkedin.com/in/julianonunes

    Lembre-se de clicar em "Votar como útil" e "Marcar como Resposta" caso tenha respondido sua dúvida.

    Remember to "Vote as Helpful" and "Mark as Answer" if your question has been answered.

    Thursday, January 25, 2018 1:45 PM
  • Hello Juliano,

    So you need get value from column A and column B and then set them as parameters to execute your query, and result will be written in Column C, right? 

    Which part bothers your? How to create the parameterized query? How to execute the query? Or how to operate on Excel sheet?

    What's type application you are developing? What type connection you are using?

    I would suggest you detail these issue so we could try to reproduce your issue.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 26, 2018 8:10 AM
  • Hello,

    I don't know how to bind the query parameter to these columns without specifying a fixed row number (because the number of rows will always change). I'm afraid my only option is to use VBA to do this, am I wrong?

    I'm using a SQL Server connection via Get External Data option on Excel.


    Juliano Nunes - http://linkedin.com/in/julianonunes

    Lembre-se de clicar em "Votar como útil" e "Marcar como Resposta" caso tenha respondido sua dúvida.

    Remember to "Vote as Helpful" and "Mark as Answer" if your question has been answered.

    Friday, January 26, 2018 12:16 PM