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
165 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:DaFranker
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now