Linking Excel to Access data
Linking Excel to Access data
(OP)
I have an Access database for customers with Customer Name, Customer Address (blah blah, all the usual fields)
I also have an Excel sheet for projects which requires the inputting of the customer name and address.
My question is this...
Is there any way to link the Excel customer name box to the Access database so it would provide me with a drop down box to select the Customer ?
In an ideal world, this would also automatically fill in the customer address fields...
Is this possible ?
I'm new to both Excel and Access so any help you could give me would be greatly appreciated.
All the best
John
I also have an Excel sheet for projects which requires the inputting of the customer name and address.
My question is this...
Is there any way to link the Excel customer name box to the Access database so it would provide me with a drop down box to select the Customer ?
In an ideal world, this would also automatically fill in the customer address fields...
Is this possible ?
I'm new to both Excel and Access so any help you could give me would be greatly appreciated.
All the best
John





RE: Linking Excel to Access data
Tim
RE: Linking Excel to Access data
I also want to expand this Excel sheet to automatically generate our invoices.
So.. unfortunately, we need to do this in the Excel. I just want the facility to have the Access as the central point for all the customer details.
Thanks.
John
RE: Linking Excel to Access data
Tim
RE: Linking Excel to Access data
RE: Linking Excel to Access data
RE: Linking Excel to Access data
I haven't started populating the database as yet. Although I've done the work on the Excel sheet. Where could I get hold a copy of QuickBooks or have a look at what it can do? Is there a website ?
Cheers
JT
RE: Linking Excel to Access data
RE: Linking Excel to Access data
Thanks for the info.. I'll give it a look.
Cheers
JT
RE: Linking Excel to Access data
Record a macro of pulling data in as a query. Open up the VBA editor and have a look at the code that was created. The name that you are looking up can be replaced in the sql statement by a variable taken from the initial spreadsheet. You will probably need to change whether it updates the range, or overwrites it (one of the options after the statement.
Link this macro to a button on the main sheet.
I will endevour to pull my code out for you if you need more help on this, but it isn't too hard, and is a useful trick to know how to do for all sorts of other things.
Cheers,
Craig
RE: Linking Excel to Access data
Thanks for the help mate. I'll try that and see how it goes.
Cheers
JT
RE: Linking Excel to Access data
From the Excel toolbar, choose Data > Get External Data > New Database Query.
From the list of data sources, select Access, then follow the fairly simple MS-Query route to obtaining the data you require. The end result will be an area of your spreadsheet that is linked with the data. You can then choose various options for that area, such as when to update the query etc.
This will only get you a relatively inflexible link to your database, but is is quick and simple. You can, of course, make the link much more flexible and conveneient by coding in VBA (as suggested above). But VBA database coding is not that straightforward, particularly for the novice. There are numerous books on the subject, my choice being "Mastering Database Programming with Visual Basic 6" by Petroutsos.
RE: Linking Excel to Access data
I had looked into that method of linking data to access but, as you say, its a little inflexible for my application.
I'll give the VB way a go and make a macro from a button as suggested. If it all goes Pete Tong, I'll turn to using QuickBooks as mentioned earlier by cbiber.
Thanks for all your help lads.
Cheers
JT
RE: Linking Excel to Access data
cpretty"remove"@talk.co.nz
I will have a go over the weekend. Sounds like a neat little project that will help my skills.
Cheers,
Craig
RE: Linking Excel to Access data
Cheers
John
(webmaster"StopSpam"@biscuitsbrown.com)