Home Case Studies MS Office Tips and Tricks Sample Code Helpful Videos Custom Development  Custom Training Join Us Contact Us
Search Our Site:  

Exchange Access and Excel Data

There are many ways to share data between databases and spreadsheets. The most powerful way is to use ActiveX Data Objects and Visual Basic for Applications to program the exchange. Data transferred this way can be exchanged based on a schedule, it can be validated before the transfer is completed and it can ‘massaged’ by either the sending or receiving application.

I Get It! Development can create software to automate these exchanges, but what if your needs are more simple? You simply want to look at a database table or the results of a query as a spreadsheet? Microsoft has been building more and more bridges between applications for quite some time.

If you are viewing a table or query results in Access, you can click on the Tools menu and use the Office Links section. To view the data in Excel, use ’Analyze It with MS Excel’ (apparently someone in marketing thought “Export To Excel’ was too confusing…)

 



A Word From Our Sponsor

PC Connection
PC Connection


Excel will automatically open and you will see the data in cells in the spreadsheet. The file is automatically saved into your default Excel folder with the same name as the table or query. If you want to find it later, be sure to use Save As… to give it a proper name and place it in a meaningful folder.

Of course, a good old copy and paste into Excel still works, too!

Be aware that data types frequently change in this operation so you might need to change them back. See our articles on forcing data type changes using Text-To-Columns and Paste Special on the web.

To put Excel data into Access, you can also use copy and paste. If you want to add data from an Excel spreadsheet to an existing table, open the table in datasheet view and use the Paste Append option that now appears under the Edit menu. 

If you are creating a new table, you can paste directly into the tables tab and Access will create the new table using the data in the clipboard.

Access also has a versatile Import menu. You can go to File > Get External Data and use it to import data from Excel spreadsheets or named ranges. You can also use this facility to import data from other databases, Outlook folders and text files.


 

References

Journal of Accountancy Articles

Tweaking The Numbers

Block That Spreadsheet Error

Excel Security Issues


 

Clients

Do you need help with Microsoft Office? Contact us

See how we've helped companies like yours develop effective business processes

Client Success Stories


 

Consultants

I Get It! Consultants set their own hours, work with their own clients, and choose their own projects.

If you're a Microsoft Office guru and would like to explore being a full-time consultant, please see our consultant information pages.