mightymike
Board Regular
- Joined
- Feb 11, 2010
- Messages
- 127
Can't work out why this is not working, can anybody help?
I have a routine which creates a pivot table, and on that table is data which I use to do a SQL query to retrieve data I need to use for something else.
So if I am correct, that sets the approver value.
Now we go and get the EMail addy
Now, I have tested this function and it works fine, but it does not seem to be passing back the EMail value to the sub routine.
I just dont get any data back
Any ideas?
I have a routine which creates a pivot table, and on that table is data which I use to do a SQL query to retrieve data I need to use for something else.
Code:
Dim Approver As String
Dim EMail As String
Approver = Sheets("Purchase Invoice Approval").Range("B1").Value
If Approver <> "" Then
EMail = GetEMailAddy(Approver)
Else
EMail = "Approver is blank"
End If
So if I am correct, that sets the approver value.
Now we go and get the EMail addy
Code:
Function GetEMailAddy(Approver As String)
Dim conn As Variant
Dim rs As Variant
Dim cs As String
Dim query As String
Dim row As Integer
Dim EMail As String
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
cs = "DRIVER=SQL Server;"
cs = cs & "DATABASE=Database;"
cs = cs & "SERVER=Server"
'parameters here are connectionString, username, password
'quotes here for this code to work.
conn.Open cs, "", ""
query = "select ExDescription.[EMail] FROM ExDescription Where ExDescription.[Description] ='" & Approver & "';"
rs.Open query, conn
row = 0
Do Until rs.EOF
row = row + 1
EMail = rs.Fields("EMail").Value
'Test to see if it works - pastes result into current active worksheet
'Cells(row, 1).Value = EMail
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Function
Now, I have tested this function and it works fine, but it does not seem to be passing back the EMail value to the sub routine.
I just dont get any data back
Any ideas?