VLOOKUP function in SQL Query

**chiran22** · 03-01-2010, 05:40 PM

Hi,
I am using VLOOKUP function in sql query. I have two sheets. I am trying to use VLOOKUP function programatically. When I click the button on excel sheet, It loads the data in two sheets. But VLOOKUP is in one of the SHEET'S sql query. But when I run It doesn't display the proper VLOOKUP value. It displays only the formula. I am using VLOOKUP like this.

SELECT roi.ganumber, roi.cont_eff_date,
'=VLOOKUP(A2,''Internal Team Data''!A:D,4,FALSE)'
FROM rpsc_opp_install roi,

It displays only the formula as it is. How can I get the data?

Thank you very much

**chiran22** · 03-02-2010, 12:18 PM

Hi,
I am using VLOOKUP function in sql query. I have two sheets. I am trying to use VLOOKUP function programatically. But when I run It doesn't display the proper VLOOKUP value. It displays only the formula. I am using VLOOKUP like this.

SELECT roi.ganumber, roi.cont_eff_date,
'=VLOOKUP(A2,''Internal Team Data''!A:D,4,FALSE)'
FROM rpsc_opp_install roi,

It displays only the formula as it is. How can I get the data? Is there any way I could get the matching data. Once the sheets were loaded with the data, Is there any way I could write a macro that runs all the cells automatically which has this VLOOKUP function.

Thank you very much

**6StringJazzer** · 03-02-2010, 01:00 PM

I am a little unclear as to how you are executing your SQL query. The only experience I have using SQL in VBA is to interface to an Access database. This interface requires you to build your query as a text string, then submit it using an established connection. It has been a while since I've done that but the code would look something like this. You have to execute the VLOOKUP as a WorksheetFunction (there is no VLOOKUP function native to VBA) and take the result and incorporate that into your query. The way you have written it just uses the VLOOKUP formula as a string rather than actually calling it, so that's what you are going to get in your result.

I haven't built a reference application to test this out, but hopefully this gives us a basis for further discussion to find out what you really need:

Dim varConnection as String
Dim varSQL as String
 
varConnection = "ODBC; DSN=MS Access Database;DBQ=C:\test.mdb; Driver={Driver do Microsoft Access (*.mdb)}" 
' Build the query based on the SQL in your post
' Note that your quotes around Internal Team Data were actually repeated 
' single quotes, but that wasn't evident from the font in your post (please use code tags).  
' I changed them to double quotes.
varSQL = "SELECT roi.ganumber, roi.cont_eff_date," & _
        WorksheetFunction.VLOOKUP(A2,"Internal Team Data"!A:D,4,FALSE) & _
        "FROM rpsc_opp_install roi" 
' Submit the query and dump result to A1
With ActiveSheet.QueryTables.Add(Connection:=varConnection, Destination:=Range("A1"))
 .CommandText = varSQL
 .Name = "Query-39008"
 .Refresh BackgroundQuery:=False
End With

**chiran22** · 03-03-2010, 12:03 PM

Thank you for your response. I am connecting to oracle database. I am getting an error when I execute the following query against oracle database.

Dim varSQL As String
varSQL = "SELECT WorksheetFunction.VLOOKUP(A2,'Internal Team Data'!A:D,4,FALSE) FROM DUAL"

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=" & qryodbc & ";UID=" & qrylogin & ";PWD=" & qryloginid & ";DBQ=" & qrydb & ";DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC" _
), Array("=10;TLO=O;")), Destination:=Range("A1"))
.CommandText = varSQL