Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Q: Excel Function that Calls SQl Query? Expand / Collapse
Author
Message
Posted Wednesday, November 20, 2013 9:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 1:34 PM
Points: 4, Visits: 23
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
Post #1516102
Posted Wednesday, November 20, 2013 11:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 1:34 PM
Points: 4, Visits: 23
For got to say, when I type "=SQLStr(....)" in excel cell I get "#VALUE!" error?.....any help would be greatly appreciated
Post #1516159
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse