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

Article translations Article translations
Article ID: 275068 - View products that this article applies to.
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.
Expand all | Collapse all

SYMPTOMS

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.

CAUSE

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.

STATUS

This behavior is by design.

RESOLUTION

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.

Properties

Article ID: 275068 - Last Review: March 29, 2007 - Revision: 5.1
APPLIES TO
  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbtshoot kbexpertiseinter kbimport kbappnote kbpending kbprb KB275068

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com