Import data from Access based on values in Excel

dafan

Well-known Member
Joined
May 6, 2008
Messages
692
Hi all,
Not quite sure if I should put this in Access or Excel...giving it a try.

For sake of this question, say I have a DB with:
1 table "COMPANY"
2 fields "COMPANY" & "PHONE"

This table contains 3 records:
- Coca Cola | 12345
- Pepsi | 67890
- Blackdrinks Ltd | 54321

Now I have an Excel sheet with in A1:
"Pepsi"

I want their phonenumber.

I can build a query in either Microsoft Query or just straight import the entire table from Access into Excel and use a VLOOKUP.
But I don't want to.

Can I automate this without using VBA? I have no idea what to put in the query to match my values in Excel.

Any help is greatly appreciated!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
If you have the information in Access, then why not do a query in Access to get the information. If necessary, you could export the query results to Excel. With just the bare minimum of information, it is difficult to give you more of an answer. What is your objective and what are your expected results. With more information, more people will chime in with potential solutions.

Alan
 
Upvote 0
Without VBA - a parameterised query. It can be set to automatically refresh on changes to your cell A1.

An old example, http://www.dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/

So, this can be a query to your database with the A1 cell applied as a filter. That is, in the WHERE clause. "SELECT whatever FROM table WHERE fieldname like [?]"

You can get a little more sophisticated with a separate query of the DISTINCT values from your field and use the list returned as data validation on cell A1 so that users can only pick an available choice. [Makes me think a slightly similar approach could be a pivot table with a page field - users change the page field. Maybe?]

HTH
 
Upvote 0
If your real situation is different in that you have more than I cell to match, then maybe use SQL like

SELECT A.*
FROM `might use Access connection info here`.tblAccess A, `and Excel connection info here`.tlbExcel E
WHERE A.commonfield = E.commonfield
 
Upvote 0
Thanks.
I figured a parameterized query would work but as I struggled building something...thankfully I have some skill in SQL so I'll try to figure it out then by Fazza's suggestion.

alan; this is for an user of us, so I don't have any more info at this point. I have to learn this myself too before I can explain it to the user.

The user later added she only needs the info once so I'm suggesting a query, then export into Excel and I'll have to teach her VLOOKUP. Shouldn't be too hard then.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top