Vlookup from Excel to Access

[dmposey61]

I am using Excel 2000 in XP. My excel worksheet has over 65535 records and will not be able to use Vlookup from another worksheet. If I have the same information in an Access database, can I pull the information (vlookup) from Access into my excel form? Basically, I enter a number into my excel form and it generates the name, address, phone number etc. Can I still enter the same number and it go to my database, find the information and still update my fields on excel?

I am new at this and not very good with VBA code, so please be very specific with your answer. Excuse me in advance, if I don't fully understand and have to ask further questions.

 

[trident22]

Yes you can. Create a named range in your Excel file which includes the cells whose value you query will be based on. Then create a new linked table in Access which is linked to this named range.

Then, in Access, create a new query to retrieve the data you require (ie SELECT NAME, ADDRESS, PHONE FROM tablex WHERE number = ExcelLinkedTableNumber).

Back in Excel go to "data", "import external data", "import data", browse to your dbase and select you new query. Choose the cell you want your quey result to appear in and you sorted.

Probably easier ways to do this but it will work.

Good luck.

 

[dmposey61]

Thanks for the reply, but I am still having difficulty with this method. Everytime I try this, it keeps trying to import the data into excel. It won't fit into excel due to exceeding the 65535 limit. I'm not quit following the 2nd paragraph: I did a new query and selected the data I wanted (name, add etc) from my original table but you lost me on "WHERE number = ExcelLinkedTableNumber". Can you walk me through that step. Sorry for being so ignorant.

But the next step is my problem. I dont want to import into excel because it is too large?

I need a little more explanation on where it is going to get the data from Access?

Thanks for being patient!
Denise