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

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.
Who is Participating?
techgrunt101Connect With a Mentor Author Commented:
Thank you for the suggestions.

This issue can be closed at this time - it was worked on using an off-line technique.
Frank WhiteCommented:
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!

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.
techgrunt101Author Commented:
I wanted to close the issue for now.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.