Want to use Excel2010 cell as primary key parameter for SQLSERVER 2008 query

[KWMKWM]

I want to use the Connection functionality in Excel 2010 to connect to a SQLSERVER 2008 database and populate column X based upon using a value in Excel column A as the primary key (loop thru the column), 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)...

Tried the following
?
&Worksheet("safety stock").range(A1:A10).value
$A$1

etc.

 

[Norie]

How exactly are you accessing the CommandText window?

Is it by using Edit Query... when you right click an existing table you've imported?

Or am I barking up the totally wrong tree?

 

[KWMKWM]

I'm travelling from the ribbon, DATA (tab), CONNECTIONS (list, choosing my connection), PROPERTIES (button), DEFINITION (tab) COMMAND TEXT (text window)

 

[Norie]

I can get there but I'm not sure how you would to introduce parameters.

I'm not even sure if CommandText in this context uses SQL, all I'm seeing is the database name and table name in single quotes.

 

[KWMKWM]

If I change COMMAND TYPE to 'sql' and type the following in the COMMAND TEXT box:

select name 
from pbaTable
where
pbaid = 'PB00000007'

I do receive the correct value from the SQLServer DB. However, I want the value 'PB00000007' and subsequent values to be in an excel column and have th query read that column for input and populate another column for output.

 

[Norie]

I found that option too and I've looked into this but I can't seem to find a way to use a parameter.

There seems to be some examples but they all seem to involve code.

I was able to set a parameter using 'standard' Get External Data... in Excel 2000.

I think there's a way to use the value from a cell with that method but to be honest I've never had much luck with parameter queries in Excel.

 

[KWMKWM]

Thanks for looking, I'm sure now it's a VBA issue.