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.
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:
-
Open the Excel workbook.
-
On the File menu, click Save As.
Note In Excel 2007, click Microsoft Office Button, and then click Save As. -
In the Save As dialog box, in the Save as typelist, click Web Page.
-
Click
Selection: Sheet option and then click Save. -
In Access, open the database into which you want to import the data.
-
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. -
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. -
Complete the steps of the Import HTML wizard.
-
Open the table in the Datasheet view.
Notice that the currency symbol appears as it did in Excel.
