Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

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

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.

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Status

This behavior is by design.

↑ Back to the top

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 typelist, 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.

↑ Back to the top

Keywords: kbprb, kbpending, kbswept, kboffice2003yes, kbsweptsoltax, kbappnote, kbexpertiseinter, kb, kbtshoot, kbfreshness2007, kboffice12yes, kboffice12vista, kbimport

↑ Back to the top

Article Info
Article ID : 275068
Revision : 3
Created on : 4/18/2018
Published on : 4/19/2018
Exists online : False
Views : 103