Create user-defined function using Querytables

[david95554352]

Hi All,

I am working on creating a user-defined function to lookup the price from SQLserver by passing in a product code. I use the Querytables but not the ODBC add-in because it is difficult to ask every user to install in their workstations.

The name of the function is "f_checkprice", the syntax is "= f_checkprice(product)", following is the content of the function:
===================================================

Function f_checkprice(v_product)

Dim sConn As String
Dim sSql As String
Dim oQt As QueryTable

sConn = "ODBC;DRIVER=SQL Server;SERVER=SERVERXYZ;UID=;APP=Microsoft® Query;WSID=;DATABASE=DB_PRICE;Trusted_Connection=Y"

sSql = "exec usp_checkprice " & Trim(UCase(v_product)) & "'"

here = ActiveCell.Address
mycolumn = Mid(here, InStr(2, here, "$") + 1, 100)

With ActiveSheet.QueryTables.Add( _
Connection:=sConn, _
Destination:=Range("C" & mycolumn), _
Sql:=sSql)
.RefreshStyle = xlOverwriteCells
.Refresh = True
End With

End Function

===================================================

The function call a stored procedures in SQLserver, following is the stored procedures:
===================================================

Create procedure usp_checkprice

@PROD as varchar(30)

as

select price from tb_price_master
where product = @PROD

go

===================================================

Here is the problem I have now:
1. The result is directed to column C, however the result always occur 2 cells. The stored procedures actually just return 1 value but don't understand why in excel there is always a blank value come with the price

2. I put the product code in column A, then refer to it in the function, ie "=f_checkprice(A1)", then drag and fill the rows. However, the remain rows does not refresh with the lookup value

Could you please share if you have any idea about how to create such a function?

Regards,
David