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


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
This article was previously published under Q275068
Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

The column format on a Microsoft Excel worksheet is lost or replaced with a different format after you import the data into Microsoft Access or create a link to the Excel worksheet.

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.
  3. In the Save As dialog box, in the Save as type list, click Web Page.
  4. Click Selection: Sheet 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.
  7. Select the HTML file that you created in step 4.

    Note 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 Datasheet view. Note that the currency symbol appears as it did in Excel.

MORE INFORMATION

By design, Microsoft Access does not retain the format of an Excel column. When you import or create a link, formatting such as the British pound, the euro symbol, and so on are lost. After the importing or the linking is finished, the data inherits the format of the regional settings that are specified in the operating system.

Steps to Reproduce the Behavior

  1. Open Control Panel.
  2. If you are running Microsoft Windows 2000:

    Double-click Regional Options, and then click the General tab. Under Your locale (location), click English (United States). Click the Currency tab, and then set the Currency Symbol box to $.

    If you are running Microsoft Windows 98, Microsoft Windows Millennium Edition, or Microsoft Windows NT 4.0:

    Double-click Regional Settings, and then click the Regional Settings tab. Under Regional Settings, click English (United States). Click the Currency tab, and then set the Currency Symbol box to $.

  3. Click OK, and then close Control Panel.
  4. Start Microsoft Excel, and then open a blank workbook.
  5. Type the following numbers in the first column:

    32
    12
    66
    41
    51

  6. Select column A, and then on the Format menu, click Cells.
  7. In the Format Cells dialog box, click Currency under Category, click English (United Kingdom) under Symbol, and then click OK. Note the new formatting of the column.
  8. Save the workbook as Book1.xls, and then quit Excel.
  9. Start Access , and then create a new database.
  10. On the File menu, point to Get External Data, and then click Import.
  11. Import the Book1.xls file that you created earlier.

    When the Import Spreadsheet wizard appears, note the format of the sample data.
  12. Click Finish to complete the import, and then click OK on the informational message informing you that the table was created.
  13. Open Sheet1 in Datasheet view. Note that the currency symbol appears as the United States dollar sign rather than as the United Kingdom pound symbol.
Modification Type:MinorLast Reviewed:6/7/2004
Keywords:kbImport kbappnote kbpending kbprb KB275068 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.