InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 11:04:44 GMT20http://www.sqlservercentral.com/Forums/Topic919889-391-1.aspxI'm a little bored, so I put together a quick excel UDF in VBA that would do what you need.This was done quickly, so there is probably a better way to do some of it, but it works. It also assumes the input and output of the SQL scalar function are integers[code="vb"]Public Function SQL_UDF(intInput As Integer) As Integer Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Dim cmd As ADODB.Command Dim stCon As String 'SQL Connection string Dim stQuery As String 'Query string 'Set ADODB requirements Set cnt = New ADODB.Connection Set rst = New ADODB.Recordset Set cmd = New ADODB.Command 'Define database connection string stCon = "Provider=SQLOLEDB.1;" stCon = stCon + "Integrated Security=SSPI;" stCon = stCon + "Persist Security Info=True;" stCon = stCon + "Data Source=SQLSERVER;" stCon = stCon + "Initial Catalog=TestDB" 'Open database connection cnt.ConnectionString = stCon cnt.Open ' Defines the stored procedure commands stQuery = "select dbo.TestFunction(" & intInput & ")" 'Define name of Stored Procedure to execute. 'cmd.CommandType = adCmdStoredProc 'Define the ADODB command cmd.CommandType = adCmdText cmd.ActiveConnection = cnt 'Set the command connection string cmd.CommandText = stQuery 'Define Stored Procedure to run 'Execute query and return to a recordset rst.Open cmd.Execute SQL_UDF = rst.Fields(0).Value 'Close database connection and clean up If CBool(rst.State And adStateOpen) = True Then rst.Close Set rst = Nothing If CBool(cnt.State And adStateOpen) = True Then cnt.Close Set cnt = Nothing End Function[/code]Tue, 11 May 2010 12:12:56 GMTA. Harmonhttp://www.sqlservercentral.com/Forums/Topic919889-391-1.aspxI don't think there is an easy way to do this. I believe you'd have to write a user defined function in Excel to make a connection to the database and utilize the scalar function from SQL. It's certainly not an option out of the boxTue, 11 May 2010 11:28:46 GMTA. Harmonhttp://www.sqlservercentral.com/Forums/Topic919889-391-1.aspxI have a scalar SQL function that I need to use within Excel. Otherwise, I'll need to use Excel formulas to do the same thing this function does, which I'd love to avoid (the function is pretty complex). Is there a way?So the function is something like:myDatabase.dbo.myFunction(integerVal).I'd like to pull integerVal from one column in Excel and put the function's result in another column. Can this be done? How?Tue, 11 May 2010 11:19:24 GMTMNsql