Need help creating User-Defined Functions in Excel to query from a database

[Brandont05]

I work in an HR department that does not have any user defined functions(custom functions).

For example: I would like to be able to use an Employee# to pull their First Name from our database.
Example Function: =GetFirstName(Employee#)

I need help writing the VBA coding necessary to facilitate this function.

Function Name: GetFirstName
Database Name: c11e
Data Table: EMPLOYEE
Data Field: FIRST_NAME

I greatly appreciate any help.

 

[BiocideJ]

What kind of database is it?
SQL Server, Oracle, Access, etc.

That information will determine what connection string will need to be created.
Also, it will be important to know the name of the Employee ID field since we are going to be looking up on that field (Employee_ID, Emp_ID, etc)

 

[Brandont05]

BiocideJ - Thank you for responding. I'm pretty sure it is an SQL Server. Opps I forgot to include that, the employee ID field is just EMPLOYEE.

 

[Brandont05]

It's kind of confusing that both the table is named EMPLOYEE and the data field is named EMPLOYEE...

 

[BiocideJ]

Not typically a good idea, but unfortunately, all too common. Not to fear, just give me some time to work something up. I don't have SQL Server so I won't be able to test my solution.

 

[Brandont05]

Great!! Thank you very much for your help.

 

[BiocideJ]

OK. This is about as close as I can get you for now.

Public Function GetFirstName(empID As Long) As Variant
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
 
    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=YOUR_SERVER_NAME_HERE;" & _
                  "Initial Catalog=c11e;" & _
                  "Integrated Security=SSPI;"
    
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("SELECT FIRST_NAME FROM EMPLOYEE WHERE EMPLOYEE = " & empID & ";")
    
    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        GetFirstName = rs!FIRST_NAME
        ' Close the recordset

    Else
        'Not found; return #N/A! error
        GetFirstName = CVErr(xlErrNA)
    End If
    rs.Close
    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
    
End Function

Credit for a majority of this code goes to Sektor from this post
http://www.mrexcel.com/forum/excel-...al-basic-applications-connect-sql-server.html

NOTE: You will still need to determine the server address of your database and insert that in the portion of my code that is highlighted red. Any issues, reply back to this thread.

 

[Brandont05]

Awesome! I'm not entirely sure how to find the server name. Do you know a quick way?

Also, I'm getting "Compile error: User-defined type not defined". It is highlighting the first line below.

Public Function GetFirstName(empID As Long) As Variant
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String

 

[BiocideJ]

I don't have SQL Server so I'm not sure I can help you there. I suspect you will need to open the database and look at the settings.

As far as the compile error, I forgot to mention that you need to add a reference for the ADODB definitions to work

Tools -> References -> Microsoft ActiveX Data Objects X.X Library
Where X.X is the highest number listed

 

[Brandont05]

I'm sorry, but I think I'm actually on an oracle server. I really thought it was an SQL sever. The reason I say this is that when I log in with my username/password it says Microsoft ODBC for Oracle Connect.