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
170 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DTS Connection Failed 7 70
SQL Querying data from 3 tables, all with 1 common column 4 36
SQL View nearest date 5 36
Increment column based of a FK 8 22
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
I have a large data set and a SSIS package. How can I load this file in multi threading?
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Viewers will learn how the fundamental information of how to create a table.

773 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