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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. 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
76,219
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
76,219
Office Version
  1. 365
Platform
  1. 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.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top