Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLGurl
Starting Member
2 Posts |
 Posted - 2012-01-09 : 11:46:57
|
| Hello,I am running a query that returns transactions that were done at a given point.For this, I am using the"connections" part of Excel.I have created a workbook connectionConnection type : OLE DB QuertCommand Type: SQLCommand Text: Selectt.SP_ID AS Salesperson ,sum(CASE t.Transaction_Date WHEN '01/05/2012' THEN (abs(t.Transaction_Units) * t.Transaction_Price etcIt runs finds and returns my data into a table in Excel.However, I need to manually update the relevant date in the Command Text.Is there a way that the connection automatically updates the date. as indicated in a cell in Excel, or maybe t-1 given that the file updates automatically every time I open?MAny thanks |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-09 : 12:26:49
|
| I was searching for some of the web pages that I have seen that explains how to do this, but could not find the exact ones I was looking for. However, this page does show you the basics. http://www.velocityscape.com/help/database_how_to_s/connecting_to_sql_from_excel.htmWhat that page does not tell you is how to use the value in a cell in Excel as the parameter to your query. In the query, where you want to use a parameter, insert a question mark. Then when you execute the query (exclamation icon), you will be prompted for the parameter value. (Alternatively, you can click the Edit Query button on the Import data dialog, which will let you specify parameters). From the properties button on the Import Data window, you can also specify a cell from where to pick up the value of the parameter. |
 |
|
|
|
|
|
|
|
Updating Date in SQL Query (Excel Connections OLE