Q: Excel Function that Calls SQl Query?

• maasultan

  SSC Rookie

  Points: 46

  More actions

  November 20, 2013 at 9:10 am

  #285371

  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

• maasultan

  SSC Rookie

  Points: 46

  More actions

  November 20, 2013 at 11:35 am

  #1667888

  For got to say, when I type "=SQLStr(....)" in excel cell I get "#VALUE!" error?.....any help would be greatly appreciated