Tough to believe I'm the only one but..I want to use the Connection functionality in Excel to connect to a SQL Server database and populate column X based upon using a value in Excel column A as the primary key, but unable to find out how.
I have tried (typing in the command text window)
select name
from pbaTable
where
pbaid = (and this is where I'm stuck)
You'd have to import values from Excel col A that you want to participate in your join with SQL Server table pbaTable col pbaid or, the other way around, import data from SQL Server table pbaTable into Excel.
Before you can join or limit data in a where clause, you need the data on the same source, or connected in some virtual manner.
Took me a while to find this too!
step 1: Put a question mark into the command text
select name
from pbaTable
where
pbaid = ?
step 2: When you refresh the query you will be prompted for the value enter a number - enter something.
step 3: Now go back to the command string in connection properties (ie. connections / properties / definition) - The parameters button should now be enabled - press it
step 4: select the parameter on the left and the "get the value from the following cell" radio button on the right.
step 4: Type in the coordinates of the cell that contains the id eg. =summary!$J$1