Query Table link input to cells

[mjacobsen]

Hello,

I have the following code to an external data source and I want to have a macro where I click the button and it links the 2 red data points to cells or an input box. So as to avoid the need for running the External query interface. If I put in a cell ref then I get an error box saying that C1 (for example) is not a valid column name.

Any suggestions?

With Selection.QueryTable
.Connection = _
"ODBC;DRIVER=SQL Server;SERVER=172.16.8.12;UID=gammabilling;PWD=gammabilling;APP=Microsoft Office XP;WSID=MJACOBSEN-DT"
.CommandText = Array( _
"SELECT vPriceInfo.Client_id, vPriceInfo.DESCRIPTION, vPriceInfo.clientservice_id, vPriceInfo.countrycode, vPriceInfo.citycode, vPriceInfo.Location, vPriceInfo.FirstUnitCharge, vPriceInfo.FirstRecurrin" _
, _
"gCharge, vPriceInfo.Startdate, vPriceInfo.Enddate, vPriceInfo.RATE" & Chr(13) & "" & Chr(10) & "FROM Financial.dbo.vPriceInfo vPriceInfo" & Chr(13) & "" & Chr(10) & "WHERE (vPriceInfo.Client_id=337) AND (vPriceInfo.clientservice_id=1) AND (vPriceInfo.Endda" _
, "te Is Null)" & Chr(13) & "" & Chr(10) & "ORDER BY vPriceInfo.countrycode, vPriceInfo.citycode")
.Refresh BackgroundQuery:=True
End With

 

[Andrew Poulsom]

Maybe try eg:

& "WHERE (vPriceInfo.Client_id=" & Range("C1").Value & ") AND (vPriceInfo.clientservice_id=" & Range("C2").Value & ") AND