ACC: How to Drag Data from MS Excel to MS Access

ID: Q141491

The information in this article applies to:

Microsoft Access versions 7.0, 97
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

This article describes a method to copy data from a Microsoft Excel worksheet to a Microsoft Access table using a drag-and-drop operation.

MORE INFORMATION

When you drag information from Microsoft Excel to Microsoft Access, you should be aware of the following data type conversion issues:

Text in Microsoft Excel appears as Text in Microsoft Access

Number in Microsoft Excel appears as Number in Microsoft Access

Currency in Microsoft Excel appears as Currency in Microsoft Access

Time in Microsoft Excel appears as Date/Time in Microsoft Access

Date in Microsoft Excel appears as Text in Microsoft Access

To drag data from a Microsoft Excel worksheet to a Microsoft Access table, follow these steps:

Open the sample database Northwind.mdb and create a new table in Datasheet view.

Start Microsoft Excel and create a new worksheet. Type the number 1 in cell A1 and type the letter A in cell A2.

In Windows 95 or Windows NT 4.0, use the right mouse button to click an empty space on the task bar, and then select Tile Vertically (or Horizontally) so that you can see both Microsoft Access and Microsoft Excel on the desktop.

Highlight the A1:A2 cells in the Microsoft Excel worksheet.

While holding down the CTRL key, point the mouse pointer at the solid line around the cells you selected until the mouse pointer becomes an arrow with a plus (+) sign. Still holding down the CTRL key, drag the selected cells to the Microsoft Access table, and then release the mouse button.

NOTE: By holding down the CTRL key during the dragging operation, you ensure that the data is copied from Microsoft Excel to Microsoft Access. If you don't hold down the CTRL key, the data is cut from Microsoft Excel. If you mistakenly cut the data, you can click Undo on the Edit menu in Microsoft Excel to retrieve the data.

When you release the mouse button to drop the data onto the table, the title of the Microsoft Access table begins to flash. This happens because the program is waiting for your response. Click anywhere in the Microsoft Access table. Note that you receive a message that states that you are about to paste two records into the table. Click Yes to confirm that you want to paste the two records.

Note that the two records are pasted into Field1 of the Microsoft Access table. To paste data into a table field other than Field1, select the fields in the table where you want the data to be placed before you start to drag the cells from Microsoft Excel. Be sure to consider that if you select only one table field, only the first column in the selection from Microsoft Excel will be pasted. If you try to repeat this and paste the second column into Field 2, Microsoft Access appends the data, rather than adds the data to the existing records. This is by design because Microsoft Access is a database program, not a spreadsheet program.


Keywords          : kbinterop IntpOff IntpOle 
Version           : WINDOWS:7.0,97
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 28, 1999