You are currently offline, waiting for your internet to reconnect

Column format on a Microsoft Excel worksheet is lost or changed when you import or link data from Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q275068
Novice: Requires knowledge of the user interface on single-user computers.

This article applies to either a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file, and to a Microsoft Access project (.adp) file.
The column format on a Microsoft Excel worksheet is lost or replaced with a different format after you import the data that is in the Excel worksheet into Microsoft Access or create a link to the Excel worksheet.
Microsoft Access does not maintain the format of an Excel column. When you import or create a link, formatting such as the British pound and the Euro symbol are lost. After the importing operation or the linking operation is completed, the data inherits the format of the regional settings that are specified in the operating system.
This behavior is by design.
These settings are not lost when you import from or link to an HTML file. For an example of how to preserve the currency formats of an Excel worksheet, follow these steps:
  1. Open the Excel workbook.
  2. On the File menu, click Save As.

    Note In Excel 2007, click Microsoft Office Button, and then click Save As.
  3. In the Save As dialog box, in the Save as type list, click Web Page.
  4. Click Selection: Sheet option and then click Save.
  5. In Access, open the database into which you want to import the data.
  6. On the File menu, point to Get External Data, and then click Import.

    Note In Access 2007, click Excel in the Import group on the External Data tab.
  7. Select the HTML file that you created in step 4.

    Notice that when the Import HTML wizard appears, the format of the sample data will not appear as expected.
  8. Complete the steps of the Import HTML wizard.
  9. Open the table in the Datasheet view.

    Notice that the currency symbol appears as it did in Excel.
prb workbook yen changed altered not the same different ACC2002 ACC2003 ACC2007

Article ID: 275068 - Last Review: 03/29/2007 18:24:44 - Revision: 5.1

Microsoft Office Access 2007, Microsoft Office Access 2003, Microsoft Access 2002 Standard Edition

  • kbtshoot kbexpertiseinter kbimport kbappnote kbpending kbprb KB275068