How to move data from MS Access to MS Excel 2010?
Microsoft Access is the database component in Microsoft Office 2010 software suite. This data is primarily in raw format and for analysis, we need to bring this data into Microsoft Excel 2010. Now there are several ways to do it. One can use the traditional Copy/Paste option i.e. the data from Microsoft Access can be copied and pasted into Excel spreadsheets, data can be exported from Microsoft Access file to spreadsheet or a data connection can be created between the data source (Microsoft Access) and data receiver (MS Excel). The choice obviously depends on how the user wants to use the data and in what format the data is in.
Copy/Paste option is perhaps the dirtiest but the fastest method of getting data. If the data is required for one time and the data is not volatile, then one can employ this method. This method, however, will not take into consideration the changes made into data once the copy/paste is done. The export data option allows you to save steps in exporting and the user can execute them again, when the data in the database (Microsoft Access) changes. Establishing a data connection is perhaps the most complete and appropriate way as it automatically updates the data when it’s modified in the original Microsoft Access source file. This is pretty useful if the user wants to analyze the latest data.
Let us look at these options one by one
A. Copying/Pasting values from Microsoft Access to MS Excel
- Open the table or query in Microsoft Access in which the data is present.
- Highlight the rows, columns or both either by dragging the cursor or using the arrow keys + Shift on the keyboard
- Press Ctrl + C on the keyboard (this is the Copy command)
- Open the MS Excel workbook where you want to paste the data
- Click on the cell in the MS Excel 2010 spreadsheet. This will be the starting point for your data
- Press Ctrl + V on the keyboard (this is the Paste command)
The data that you have copied might not be in proper format and may require formatting so that it becomes easier to sort and preserve data for further analysis.
 |
B. Exporting data to MS Excel 2010
I like the second choice when migrating data from Access to Excel. There is also a wizard in Microsoft Access that helps you to export data to MS Excel. One can use data from any table, query, form or report in Microsoft Access to be exported. The good things about this export option are that it allows the user to retain formatting and it also allows selection of fragment of data. This means that if the user likes, he can use selective data for analysis.
The word of caution here is the fact that the user needs to close the receiving MS Excel file in order to use this Export Wizard. The user should click External Data tab and the click Export group. In the export group, he should click Excel. Here is the order:
External Data > Export > Excel
C. Creating a data connection
This is the last option to export data from MS Access 2010 to MS Excel. One should store the database (Microsoft Access) in a location that is convenient to remember and search. The word of caution here is to use regular Microsoft Access databases and not web databases (as they aren’t supported).
In MS Excel, click Data in the front tab and then point your cursor to External Data Group, click Microsoft Access (for setting up new connection) or you can click Existing Connections (if you have already setup a connection before).
Import Access into Excel – Related Links
–http://www.howtogeek.com/80118/import-microsoft-access-data-into-excel-2/
–http://www.shareasale.com/r.cfm?b=351152&u=581037&m=35335&urllink=&afftrack=
If you would like more information on Microsoft Excel, please visit our Excel 2010 tutorial page.
LearningComputer.com is an e learning company with training on products like Microsoft Windows, Microsoft Office, Mozilla Firefox, Internet Explorer, Visual Basic, Java, SQL Server, Internet Marketing, SEO and many more topics on IT training and computer learning.
