VLOOKUP(sorta) against SQL Server table - help!

[cpa-mcse.net]

Simplified scenario:

I have column A and column B with multiple rows of variable data in the
column A cells (i.e. account numbers). I want to return a value to the cell
in column B pulled from a single SQL Server table which depends upon the
value in the column A cell (i.e. account names).

It's essentially the same as a VLOOKUP against a SQL table rather than an
Excel range. VLOOKUP will not work, so I am looking for other solutions.

I am using Excel 2007. I am aware of the SQL.REQUEST function as a clumsy
but workable solution, but it is not supported in Excel 2007.

Anyone up to the challenge? Thanks in advance!

 

[Ross Culver]

Can you use VBA?

Ross

 

[cpa-mcse.net]

Hello,

Unfortunately, I am not a VBA guy. I researched that option and VBA code
would require static cell references (???) which would not work for me.
Essentially, I want to be able to copy this "formula" to anyplace in the
worksheet and have it pull info from SQL based upon the value of the cell to
the left of it.

 

[Ross Culver]

You could, of course, still do this in VBA. In fact, I'm not sure you have
any other choice since there's no 'Get External Data' approach for multiple
parameters as you've described. Below is a sample of what the vba code
might look like.

Dim param 'This will be used for your column A value
Dim str As String, cnn As New ADODB.Connection, X As Integer, cnnStr As
String

cnnStr = "Data Source=SQLDatabaseServer;Initial Catalog=SQLDatabase;Persist
Security Info=True;User
ID=sa;Password=whatever;MultipleActiveResultSets=True;Connect Timeout=180"
X = 2 ' Assuming your first A value is in row 2
cnn.ConnectionString = cnnStr
cnn.Open

Do while Sheets("Sheet1").range("A" & x).value <> ""
Dim rst As New ADODB.Recordset
rst.ActiveConnection = cnn
rst.CursorLocation = adUseClient

param = Sheets("Sheet1").range("A" & X).value

str = "Select * from tableA where fieldA = '" & param & "'"
rst.Open str
If Not rst.EOF Then
rst.MoveFirst
Do While Not rst.EOF
Sheets("Sheet1").Range("B" & X).Value = rst.Fields.Item(1).Value
'rst.MoveNext ' Watch this! If you know there's only one value
that can be returned from SQL then there's no need to loop through the
recordset. If you want to return multiple values,
' then use another variable to set the row
to write to.
Loop
End If
rst.Close
x = x + 1
loop

cnn.Close

Make sense?

Ross

 

[cpa-mcse.net]

Thanks for the code. I will try it out. I also received this VBA solution:

http://www.mrexcel.com/board2/viewtopic.php?p=1459805#1459805

I'm not a VB guy, but hopefully I will get either (or both) to work.