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

KWMKWM

New Member
Joined
Jun 3, 2010
Messages
12
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
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

New Member
Joined
Jun 3, 2010
Messages
12
I'm travelling from the ribbon, DATA (tab), CONNECTIONS (list, choosing my connection), PROPERTIES (button), DEFINITION (tab) COMMAND TEXT (text window)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
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.:eek:
 

KWMKWM

New Member
Joined
Jun 3, 2010
Messages
12
If I change COMMAND TYPE to 'sql' and type the following in the COMMAND TEXT box:

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
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.:)
 

Forum statistics

Threads
1,085,495
Messages
5,384,004
Members
401,871
Latest member
allemandi

Some videos you may like

This Week's Hot Topics

Top