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…)
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.