I have an Excel sheet with 3 columns: PartNo, Cost and Price.

The PartNo column contains values that a user has entered. The other 2
columns (Cost and Price) contain no values currently.
I would like to populate the Cost and Price columns with values from a SQL
database using the value in the PartNo column.

I am guessing that I will loop through the PartNo column and query the
database for each PartNo to obtain the Cost and Price.

How can I retrieve values from the database and place those returned values
in their respective columns? Is there a way to perform the query and have
the results of the query stored in a variable?

I would rather place just the value in the respective column and not have
any "link". I have tried SQL.REQUEST which worked, but I am not crazy about
the fact that it refreshes automatically.

Thanks

Hello Kevin:
The other problem with SQL.REQUEST is that it needs to connect, process, and
disconnect each time it calculates.
What you really need to do is use an ADO disconnected recordset in VBA. If
you are not familiar, ADO=ActiveX Data Objects and provides an object model
for working with external data sources. You create a connection to your SQL
database, create a recordset based on an SQL query, and then close the
connection while saving the recordset (and the query results). If the
recordset is saved as a Public object variable, it can be used in a user
function you could write to find specific data (i.e. to match your Part Nos).

Too much to go into here, but you can check out the MSDN library. Here's a
start (and then try a search for "ADO disconnected recordset")
http://msdn.microsoft.com/library/de...entid=28001860

"Kevin L" wrote:

I have an Excel sheet with 3 columns: PartNo, Cost and Price.

The PartNo column contains values that a user has entered. The other 2
columns (Cost and Price) contain no values currently.
I would like to populate the Cost and Price columns with values from a SQL
database using the value in the PartNo column.

I am guessing that I will loop through the PartNo column and query the
database for each PartNo to obtain the Cost and Price.

How can I retrieve values from the database and place those returned values
in their respective columns? Is there a way to perform the query and have
the results of the query stored in a variable?

I would rather place just the value in the respective column and not have
any "link". I have tried SQL.REQUEST which worked, but I am not crazy about
the fact that it refreshes automatically.

Thanks