×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Linking Excel to Access data

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

RE: Linking Excel to Access data

Do you need to use excel? I'm new to access and and using excell for more than a calculator with many cells to store/put data. But I just learned how to make pull down tabs in Access to do what your talking about in acess alone.  If you say it will help I'll try to explain how to do that.

Tim

RE: Linking Excel to Access data

(OP)
Thanks for the reply.. unfortunately I do need to use Excel, the reason being that the project sheet also costs the jobs done to that particular project (ie. hours spent on job at whatever rate, parts bought etc etc)

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

The other things you mention could be done in access but I see your point.  Good luck.

Tim

RE: Linking Excel to Access data

(OP)
Thanks for your help Tim. Anyone else got any ideas about this ?

RE: Linking Excel to Access data

I use financial software (QuickBooks) to do what you're after -- but probably not to the complexity you need. Plus you'd have to move the database manually if it's in Access... never mind!

RE: Linking Excel to Access data

(OP)
cbiber

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

Try quickbooks.com. I got mine at my local computer & office equipment superstore. It was about $250 if I remember correctly and it'll save you a whole lotta programming...

RE: Linking Excel to Access data

(OP)
cbiber

Thanks for the info.. I'll give it a look.

Cheers

JT

RE: Linking Excel to Access data

I have done something similar to this pulling data out of access.  I used the query tool in excel to query the database on another sheet, then  put the data across using some simple VBA.

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

(OP)
Craig

Thanks for the help mate. I'll try that and see how it goes.

Cheers

JT

RE: Linking Excel to Access data

There are relatively simple ways of getting data from Access (and other database sources) into Excel.

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

(OP)
tomatge

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

If you send me a copy of your database

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

(OP)
No worries, I'll get a copy over to you. Although I don't think it will be tomorrow because I'm out on site. I'll certainly get it over to you before w/e though and you can have a look.

Cheers

John
(webmaster"StopSpam"@biscuitsbrown.com)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources