Access populating Excel

[pengla]

I posted this problem a month or so ago on the Excel message board and thanks to Pennysaver and LEXCERM for their reply. The question I was asking which was not very clear was how to do the following if possible using VBA so the user only has to click one macro button.

I am new to Access and Excel VBA so am not too sure if this is possible. I have got an Access Database with a table called Customers which holds name, address and postcode.

I have an Excel Spreadsheet with cells that hold name, address and postcode.

What I would love to do is be able to enter the name in a cell in Excel click a macro button and the address and postcode is populated from the database automatically.

The response I got form Pennysaver and LEXCERM was to use import data and go through that procedure. That worked but would be too complicate for the user I am giving the system to.

Hope this make sense and sorry if I am being a pain in asking this again. No one on the excel message board responded so I though I would ask the Access experts. Cheers in advance
Pengla

 

[Gates Is Antichrist]

First, you can save yourself mondo effort, and avoid all coding, by simply linking a table to a worksheet. All you'd need to do is copy/rename the new client spreadsheet to the same name each time you receive a client update, and ensure that the worksheet name and format/layout doesn't change. Shockingly (to me), Microsoft did this "right" - it's quite versatile - Access can be closed when the worksheet changes; and even if the worksheet is closed, opening Access will recognize the changed data anyway. (Having both open might improve performance):
http://www.mrexcel.com/archive2/70300/81509.htm
-------------------
Okay, you asked for a coding solution so here it is. If you know simple SQL look at these three and try them out, and ask again if you're stuck. (Hey, if someone wants to supply the QueryDef [instead of SQL string] code to run a saved Access query, please do so):

I prefer DAO, but ADO is also shown here:
http://www.mrexcel.com/archive2/70300/81541.htm

more verbose:
http://www.mrexcel.com/archive2/22800/25965.htm

Another example:
http://support.microsoft.com/?kbid=145826
-------------------
The Erlandsen site is always wonderful for this kind of code question:
http://www.erlandsendata.no/english/index.php?d=envbadacexportdao
-------------------
Comprehensive source of solutions:
http://www.mrexcel.com/vbabook.html

 

[pengla]

Cheers

Thank you for your help it work a treat. Sorry for not replying sooner I was away. Cheers again