Microsoft Access - Access 2007 Import from Excel with formatting

Asked By D on 13-Dec-11 11:39 AM
I have an Excel file that has text formatting:  e.g., color, underline, etc.

Is it possible to import the file into an Access table and retain the formatting?

I know within Access I can change the field to rich text and do the formatting there, but I don't want to have to redo the formatting - I want to import the formatting.

Jitendra Faye replied to D on 13-Dec-11 11:48 PM
You can import Excel file to access but you can not retain formatting because after importing data will be saved in form of table and in table you can not set color for values. 
D replied to Jitendra Faye on 14-Dec-11 09:10 PM
If I set the table field to rich text I can format the field text in color, bold, etc.

But I want to be able to import that formatting from the Excel file.

Pat Hartman replied to D on 03-Jan-12 03:06 PM
Excel merges the data and presentation layers for display purposes so it looks like the data is formated.  In reality, it separates the formatting from the data behind the scenes.  When Access imports a spreadsheet, it only has access to the "data" portion.  It cannot "see" the formatting.  If you were to use OLE to automate the import rather than relying on TransferSpreadsheet, you would have more control and you would be able to examine the cell formatting.  I don't believe you would still be able to get to the embedded formatting which is either HTML or RTF (probably HTML with newer versions).   So, I don't have an answer.  What I would do is to investigate Excel's ability to create actual HTML or RTF files.  Then I would import the data from those files.  Good luck.  Please post back with your solution if you find one.