November 20, 2013 at 9:10 am
Hi everone,
I am trying to write a function as excel add-ins that uses sql query to aggregate data (SUM for ex). The function 5 paramaters, as you see below, are:
tabl_from (table name),
con1L_where (column name),
con1R_where (text or id, note that: con1L_where = con1R_where),
con2L_where (column name),
con2R_where (text or id, note that: con2L_where = con2R_where)
Here is the function:
Public Function SQLStr(tabl_from As Variant, con1L_where As Variant, con1R_where As Long, con2L_where As Variant, con2R_where As Long) As Long
' SQL Server Connection
'
' FOR THIS CODE TO WORK
' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
'
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Server_Name = "D01ODMMATLAB01P\ODM" ' Enter your server name here
Database_Name = "MICROSOFT SQL SERVER " ' Enter your database name here
SQLStr = "SELECT SUM(SOMA_AWARD_AMT) FROM tabl_from WHERE con1L_where = con1R_where AND con2L_where = con2R_where " 'Enter your SQL here
Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=SQLOLEDB;Data Source=D01ODMMATLAB01P\ODM;INITIAL CATALOG=TDMS;INTEGRATED SECURITY=sspi;"
Cn.Open
rs.Open SQLStr, Cn, adOpenStatic
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Function
Question: Are the function parameters compatiable with how they appear in SQLStr? In Excel cell, will type something like: "=SQLStr(AUCTIONS,SEC_TERM_DESC,13-Year,COUPON_PYMT,BILLS)"
Thanks alot,
Mike
November 20, 2013 at 11:35 am
For got to say, when I type "=SQLStr(....)" in excel cell I get "#VALUE!" error?.....any help would be greatly appreciated
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply