locked
How to extract one single value from a query and place it in a cell. Not creating a table, just the value RRS feed

  • Question

  • Hi all. 

    I need to place individual values coming from a query in specific cells. The query pulls data from an external source, which returns just one single row. I need to extract and display just one of the values.

    The query result looks like this:

    ProjectName being the value that I want to display in a cell (not as a table with one single value)

    I have tried to create a new query based on that field, which creates the query returning that single value. But then loading the query on the sheet, it converts it into a table (or pivot if I wanted to), but this is NOT what I want:

    What I need instead, is just the value in the cell, which I can format and place where I want. Something like if a formula could get a value from a query/connection, something like "=GETQUERY("ProjectName")", if you know what I mean

    Thanks!


    dp

    Wednesday, January 2, 2019 6:08 PM

Answers

  • The data that comes into Excel from PQ does so by means of a OLE DB WorkbookConnection object... so I don't know if it's possible to return the results from that in a more direct way using VBA or something maybe?

    Realistically though, the easiest way is to just throw the queries with single values you want onto a hidden sheet and reference the values from the output tables wherever you need them in your workbook.

    • Proposed as answer by Colin Banfield Wednesday, January 2, 2019 8:15 PM
    • Unproposed as answer by Colin Banfield Wednesday, January 2, 2019 8:15 PM
    • Proposed as answer by Colin Banfield Wednesday, January 2, 2019 8:15 PM
    • Marked as answer by dpiret Thursday, January 3, 2019 10:27 AM
    Wednesday, January 2, 2019 7:58 PM
  • Realistically though, the easiest way is to just throw the queries with single values you want onto a hidden sheet and reference the values from the output tables wherever you need them in your workbook.

    That would be a useful option, since any query must be returned as a table to Excel (or pivot table, which makes no sense in this case).

    The only other thing that I can think of (more directly) is to remove the header row from the returned table and use a table style with no formatting. However, because it's still a table, you will be left with the drag icon on the bottom right of the cell.


    Apologies for the Proposed/Unproposed/Proposed stuff. I first clicked "Propose as answer" and meant to click "Quote" next, but ended up clicking "Unpropose as answer" in error. :)
    • Edited by Colin Banfield Wednesday, January 2, 2019 8:29 PM
    • Marked as answer by dpiret Thursday, January 3, 2019 10:27 AM
    Wednesday, January 2, 2019 8:24 PM

All replies

  • The data that comes into Excel from PQ does so by means of a OLE DB WorkbookConnection object... so I don't know if it's possible to return the results from that in a more direct way using VBA or something maybe?

    Realistically though, the easiest way is to just throw the queries with single values you want onto a hidden sheet and reference the values from the output tables wherever you need them in your workbook.

    • Proposed as answer by Colin Banfield Wednesday, January 2, 2019 8:15 PM
    • Unproposed as answer by Colin Banfield Wednesday, January 2, 2019 8:15 PM
    • Proposed as answer by Colin Banfield Wednesday, January 2, 2019 8:15 PM
    • Marked as answer by dpiret Thursday, January 3, 2019 10:27 AM
    Wednesday, January 2, 2019 7:58 PM
  • Realistically though, the easiest way is to just throw the queries with single values you want onto a hidden sheet and reference the values from the output tables wherever you need them in your workbook.

    That would be a useful option, since any query must be returned as a table to Excel (or pivot table, which makes no sense in this case).

    The only other thing that I can think of (more directly) is to remove the header row from the returned table and use a table style with no formatting. However, because it's still a table, you will be left with the drag icon on the bottom right of the cell.


    Apologies for the Proposed/Unproposed/Proposed stuff. I first clicked "Propose as answer" and meant to click "Quote" next, but ended up clicking "Unpropose as answer" in error. :)
    • Edited by Colin Banfield Wednesday, January 2, 2019 8:29 PM
    • Marked as answer by dpiret Thursday, January 3, 2019 10:27 AM
    Wednesday, January 2, 2019 8:24 PM
  • Thanks, both solutions "kind of" work. I think I'll go for hidden sheet when I have several values to work with, and unformatted table otherwise.

    Just in case someone from Microsoft listens: I would like to be able to reference a value in a data collection the same way I do with a table (without any VBA)


    dp

    Thursday, January 3, 2019 10:42 AM