Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

From an Excel file, query a SQL table for a field based on the value of another Excel cell's contents.

Posted on 2012-03-15
4
Medium Priority
?
177 Views
Last Modified: 2012-06-27
I see similar articles but I am not able to figure this out.

I have a simple SQL table with specific columns (like FULLNAME and PHONENUMBER). From Excel, users will type a FULLNAME value into the cells in column “A” and I need Excel to automatically execute a query to retrieve the associated PHONENUMBER and display that in another cell (into column “B”). I do not need to display a selection list to the user, I simple need to poulate Excel.

Can you direct me to a good source? I know how to accomplish this outside of Excel, but I do not know how to do this from within Excel and this needs to be accomplished from with Excel.

Thank you for any direction you can provide.
0
Comment
Question by:techgrunt101
  • 2
4 Comments
 
LVL 3

Expert Comment

by:Frank White
ID: 37725828
This requires some VBA code. (or another program running constantly and checking in on Excel through ActiveX regularly, but that's needlessly complex in my opinion)

You can use the Worksheet_Change() event with an Application.Intersect validation to see when a user enters data in your "A" column.

For the SQL query, the only way I know of is through ActiveX, which might need specific settings in the MS SQL Server (I'm not very familiar with MSSQL, so I can't really help on that end - but I'm sure other experts here can). You'll have to make sure you've got the right reference libraries linked for that.

For an example of how to use this, check this thread here!
0
 
LVL 9

Expert Comment

by:armchair_scouse
ID: 37726067
Hi,

I've knocked up an example for you to look at.  The attached file has a button on Sheet1.  You type a value in a cell, then click the button.  This calls a macro assigned to the button, cmdClickMe_Click, which in turn, calls a piece of code in a code module, basMain.GetRiskDetails.  The result is that if the value entered on Sheet1 is recognised, then fields from the matching record found in the database are output next to the value on Sheet1.

You'll need to update the ConnectionString variable where I have put <YourServerName> and <YourDatabaseName>, and you will also need to put a correctly formed SQL string as the CommandText instead of
SELECT PHONENUMBER, FieldName1, FieldName2, FieldName3 FROM tblSomeData

This should get you going.
XLandSQLServer.xlsm
0
 

Accepted Solution

by:
techgrunt101 earned 0 total points
ID: 37803627
Thank you for the suggestions.

This issue can be closed at this time - it was worked on using an off-line technique.
0
 

Author Closing Comment

by:techgrunt101
ID: 37820913
I wanted to close the issue for now.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question