CODEWITHSUNDEEP
sqlexcelvba
HansDampf
HansDampf

Reputation: 121

How to change SQL WHERE clause through excel?

I have build a SQL query to provide me historical price data of a product, which I intent to use in excel (pivot, graphs, all of that fancy excel stuff).

The problem now is that, due to the nature of many products and many price changes, I can not get all the products loaded that I intent to.

I somehow need to tell excel that has to change a couple numbers in the connected SQL query, i.e. through a text box and then load the query again. Otherwise I will always open up the query editor in excel and change it manually, which takes quite a bit.

I reckon I will have to use some sort of macro or VBA, but I have never used it. If anyone could refer an article that would be great, as i could not find anything helpful.

Some code:

  WHERE


 PD.Product_Id = '11761476'  < I will have to change that number  


AND

PSPH.[Valid_To] > '2018-01-01'


ORDER BY 
PSPH.[Valid_To]

Upvotes: 0

Views: 137

Answers (2)

user10127657
user10127657

Reputation:

You can put this instead in the connection:

WHERE
PD.Product_Id = '11761476'  < ? 
AND
PSPH.[Valid_To] > '2018-01-01'
ORDER BY 
PSPH.[Valid_To]

And then save the connection.

The first time it will run, a prompt will ask you where to find the parameter, and you can choose the cell.

Let me know if it works!

Cheers,

Arnaud

Upvotes: 0

CLR
CLR

Reputation: 12279

To manipulate the SQL string, you could do something like this..

pid = "11761476"
validto = "2018-01-01"

SQLtemplate = "WHERE PD.Product_Id = '[prodID]' AND PSPH.[Valid_To] > '[validto]' ORDER BY PSPH.[Valid_To]"

Sql = Replace(SQLtemplate, "[prodID]", pid)
Sql = Replace(Sql, "[validto]", validto)

but before you can use that, you'll need to follow @Foxfire's advice and record a macro while you're changing it manually to see exactly what needs to change, and how.

Upvotes: 1

Related Questions