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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy