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
173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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