Passing values into SQL script from Excel

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Passing values into SQL script from Excel

Author

Topic

Azerty99
Starting Member

2 Posts

Posted - 2009-04-23 : 11:02:00

My first post

I am working with Excel drawing data from SQL 2005 via ODBC. Is there a way of passing a parameter from Excel into the SQL script to draw the data, for example:

Dyanamically add the result of a drop down combo-box to a where statement to run a select from a SQL table

?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-04-23 : 11:34:24

There is, but this is really more of a vba question. You can write code to get the parameters from the spreadsheet and then build the SQL in VBA, and then pass that into your server.

StartDate = "'" & Range("Params").Cells(1) & "'"
AsOfDate = "'" & Range("Params").Cells(2) & "'"

wsData.Columns.Clear

ConnStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=yourDatabase;Data Source= yourServerAdress"
sqlString = "EXEC dev.yourSProc " & StartDate & "," & AsOfDate
 
With cnDaily
    .ConnectionString = ConnStr
    .CommandTimeout = 0
    .Open
 End With

 
With rsTriangles
 
    .ActiveConnection = cnDaily
    .Open sqlString
     Sheets("Data").Range("F5").CopyFromRecordset rsTriangles
    .Close
End With

You can also go thru data--> get external data --> new database query and set up the parameters in there.

Jim

Azerty99
Starting Member

2 Posts

Posted - 2009-04-23 : 13:23:08

Thanks Jimf. I have found the parameters option and used it successfully.

I am still interested to explore the VBA option, although my SQL is way better than my VBA!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources