1

I read all the posts with similar questions but couldn't find a working solution.

What I try to do is take value from excel cell and put it in a SQL query (again in excel) so when the value is changed, the SQL query is also changed accordingly.

For example I have two sheets in a workbook and in Sheet 1 I have two dates in cells a2 and b2. I want to pass them in a SQL query so the data extracted is constrained between these two dates.

The query I tried in different syntax variants is:

SELECT
   [INSTANCEDATE] 
FROM
   [hrsb].[dbo].[cVBAY] 
WHERE
   INSTANCEDATE >= [sheet1$a2]
AND INSTANCEDATE <= [sheet1$b2]

This does not work. I usually get an error that column b name is invalid. Seems that it doesn't make difference between excel values and DB parameters.

Of course, if instead of reference to excel cell values, I put just date range it works.

Could you please advise how this can be done?

Aztk
  • 13
  • 1
  • 4
  • 1
    I think we need some more information: You stated that you're doing the sql query in Excel. How? Is it through VBA coding? Power Query? Microsoft Query? The method being used can greatly affect an answer's usefulness. – Mistella Sep 16 '19 at 15:47
  • I think here is the problem - I thought it is enough to add [sheet1$a2] in the query in order to pass the value from the cell. Actually I don't use none of the above. What I do is to open the saved query from existing connections in excel, make only connection (without doing the actual DB extract) and edit the connection by adding in properties - command text the above. Probably this is my mistake. Please correct me. – Aztk Sep 17 '19 at 07:07
  • From the description in your comment, I believe you are using Microsoft query. I would recommend taking a look at this [answer](https://stackoverflow.com/a/26443279/9259306) as I was able to successfully add a parameter from an Excel cell to a simple query by following it's instuctions. – Mistella Sep 17 '19 at 12:46
  • Thanks a lot! I'll try and get back to report the results. – Aztk Sep 17 '19 at 13:19
  • Mistella, your answer works. The query is converted in another format / syntax by excel and works. Thank you! – Aztk Oct 03 '19 at 08:59
  • I'm glad you got it figured out. :) – Mistella Oct 03 '19 at 13:26
  • Possible duplicate of [How to use parameterized query in Excel using column as parameter?](https://stackoverflow.com/questions/26413092/how-to-use-parameterized-query-in-excel-using-column-as-parameter) – Mistella Oct 03 '19 at 13:27

0 Answers0