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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
7,142
Office Version
  1. 365
Platform
  1. Windows
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
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 

dafan

Well-known Member
Joined
May 6, 2008
Messages
692
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.
 

Forum statistics

Threads
1,176,416
Messages
5,902,969
Members
435,002
Latest member
chenmoti

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
Top