How to link Access To Excel Files

[pccarrick]

Hi could somebody please advise if the below is possible.
I have several excel spreadsheets with accounting information in and i would like to read part of this information into Access so that the user has a single source to read this information rather than having to open excel and access for reading.
I would also like to push some of the information back into another spreadsheet so that the data in that one is updated. I know that you can link to excel to read the information in the excel spreadsheet and this information will be correct if the spreadsheet is update, not to sure how to push it out into another spreadsheet though. Also i will have several of these files that are located in different folders so the locations maybe different.

I would be greatful if somebody could advise if this is possible and the best route i need to take to make this work.

Thanks in advance.

 

[kjv1611]

You're asking a very generic question, so all you can expect is a very general answer. Yes, it's possible. You can import, export, link, etc...

If you want a more specific answer, ask a more specific question. Also, tell us what version of Access/Excel you're using.

And beside that, you probably need to ask in another forum. If you are just mainly dealing with tables and relationships, then that's the forum to go to. Or if you're looking for a way to automate this via code, then the VBA forum.

forum700

forum705

 

[pccarrick]

Hi thanks for the links and response.

Another explination of what i am trying to do is as follows

i have c:\data\spreadsheet1.xls & c:\files\spreadsheet2.xls which i want to display Cells A1 - A3 which has cost values, i want these to be shown on a form within access.

I also have c:\update\spreedsheet3.xls which i want to update with the values from spreadsheet1 & 2 from access this would be automated so if the values of spreadsheet1 & 2 change this would reflect on spreadsheet3.

All updates to spreadsheet1 & 2 will be done within excel but changes reflected within access and changes to spreadsheet3 pushed from access automaticaly.

Access 2003 is currently used for this. Hope this explains what i am trying to do a little bit more specific.

Thanks and any help will be appreciated.

 

[kjv1611]

Well, I suppose you are dealing with static worksheets and static locations, then? If so, then..

1. Import the source worksheets as linked tables.
2. For your Form, you can create a query which combines what you need from the 2 tables, and then set that query as the rowsource for your form.... each field's rowsource will be whatever table/query field you want to show in that position/location.

3. For the export, Easiest would be to open the Excel workbook, and "import" from Access, and set the query settings to "refresh on open" or "auto refresh" - I forget the wording, and I've got Access/Excel 2007, so it may be different in 2007 vs 2003, so I can't look to be sure.

Are you talking about doing something more than this, or am I just missing something, or does this basically explain what you want to do?