Copy Paste Data from Excel to Access2007

Introduction.

Microsoft Office Applications already provide built-in methods to transfer data between them. This can be implemented by importing or exporting data, directly linking to the source while keeping the original data intact in the parent application, or by simply copying data to the clipboard and pasting it into another program. These options have long been available.

In earlier versions of Access, you first needed to create a table with matching field types before pasting or appending data. Access 2007 has made this process much simpler. For example, you no longer need to create a table beforehand when pasting data from Excel. Instead, Access 2007 prompts you to confirm whether the copied data includes header rows. If you select Yes, Access automatically creates a new table (using the worksheet name) and pastes the data into it, assigning the correct field types.

Let us find out how?

1. Open Microsoft Excel and create a small database with the sample data given below:

   

   Right-click to open Large Image in New Tab/Window.

2. Open Microsoft Access 2007 and open an existing .accdb database or create a new one.

3. Make the Excel database window active.

4. Highlight the Excel database range, including the header row.

5. Select Copy from the Home Menu, to transfer the data into the Clipboard.

6. Make the Access 2007 database window active.

7. Right-click on the Navigation Pane of Tables and select Paste from the shortcut menu.  The following message box is displayed:

   

   Right-click to open Large Image in New Tab/Window.

8. If you have included the header line of the data while coping it, then you may click on the Yes Command Button; otherwise, select No.

A new Table will be created with the Worksheet name.  The header cell values will be used as field names.  The field data type (Text, Date, Number, etc.) will be correctly defined depending on the data type that you have copied from Excel.

If you have selected No, then the data will still be pasted into a new table, but the field names will be F1, F2, F3, etc.

Technorati Tags: Microsoft

1. Roundup Excel Function in MS-Access
2. Proper Excel Function in Microsoft Access
3. Appending Data from Excel to Access
4. Writing Excel Data Directly into Access
5. Printing MS-Access Report from Excel
6. Copy-Paste Data From Excel to Access 2007
7. Microsoft Excel-Power in MS-Access
8. Rounding Function MROUND of Excel
9. MS-Access Live Data in Excel
10. Access Live Data in Excel- 2
11. Opening an Excel Database Directly
12. Create Excel, Word Files from Access