Import data from Access based on values in Excel

[dafan]

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!

 

[alansidman]

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

 

[ndendrinos]

maybe this will help:
http://www.mrexcel.com/forum/showthread.php?t=179936&highlight=ndendrinos&page=2

Just noticed you are using Excel 2010 so not sure if this will work for you.

 

[Fazza]

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]

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]

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.