Most difficult problem I've encountered: SQL/Excel Connection

[MarkoExcel]

Hi Everyone

This is the first time I am using this forum to ask a question about something I have spent a long time trying to do with not much luck.

Essentially all I want to do is create a procedure/function in Excel such as =SQLdata(3), where 3 could be customer ID and then the function would make a connection to SQL and perform the SELECT etc procedure and return the CustomerName in the cell with that formula.

I have excel 2007 and MS SQL where my data is stored. I understand that I need to make the connection to my SQL database, but I really do not know how to do this.

Any help would be greatly appreciated.

Thanks soo much in advance!

 

[RoryA]

The code will be something like this, depending on your exact setup:

Function SQLData(lCustID As Long) As String
   Dim cn As Object
   Dim strQuery As String
   Dim rst As Object
   Dim i As Long
   
   Const adOpenKeyset As Long = 1
   Const adLockOptimistic As Long = 3
   Const adCmdText As Long = 1
   
   ' change table and field names as needed.
   strQuery = "SELECT CustomerName FROM table_name Where CustomerID = " & lCustID
   
   
   Set cn = CreateObject("ADODB.Connection")
   With cn
      .Provider = "sqloledb"
      .ConnectionString = "Data Source=server_name;Initial Catalog=database_name;Integrated Security=SSPI;"
      .Open
   End With
   
   Set rst = CreateObject("ADODB.Recordset")
   rst.Open strQuery, cn, adOpenKeyset, adLockOptimistic, adCmdText
   With rst
        If Not .EOF Then
            SQLData = .Fields(0).Value
        Else
            SQLData = "Not found"
        End If
        .Close
   End With
   cn.Close
End Function

 

[MarkoExcel]

You my friend are a genius. There is so much code out there but after trying for weeks to get this to work, I gave up and had to ask someone. I really appreciate you help - the code you provided worked great and does exactly what I wanted.

I just wanted to ask one other question - If I want to pass a CustomerID that has numbers and letters, such as '1a2b3c', what would I have to change for the code to work? I tried a few changes, but I keep getting #VALUE!

Thanks again!


=SQLData(1a2b3c)

--------------------

Function SQLData(lCustID As String) As String
Dim cn As Object
Dim strQuery As String
Dim rst As Object
Dim i As Long

Const adOpenKeyset As Long = 1
Const adLockOptimistic As Long = 3
Const adCmdText As Long = 1

' change table and field names as needed.
strQuery = "select [customer name] from [dbo].[Person] where [CustomerID] = " & lCustID

Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "sqloledb"
.ConnectionString = "Data Source=shaban-pc;Initial Catalog=Family;Integrated Security=SSPI;"
.Open
End With

Set rst = CreateObject("ADODB.Recordset")
rst.Open strQuery, cn, adOpenKeyset, adLockOptimistic, adCmdText
With rst
If Not .EOF Then
SQLData = .Fields(0).Value
Else
SQLData = "Not found"
End If
.Close
End With
cn.Close
End Function

 

[RoryA]

If the field is not numeric, you have to pass it enclosed in single quotes in the SQL string:

strQuery = "select [customer name] from [dbo].[Person] where [CustomerID] = '" & lCustID & "'"

 

[MarkoExcel]

That's Perfect
Thanks again - I really appreciate it!