Import HTML cell from Excel into Access table using TransferSpreadsheet

[EddieN1]

I have a process where I use TransferSpreadsheet with acImport to transfer an Excel worksheet into an Access table. One of the columns contains HTML or Rich Text formatted data. When I run the TransferSpreadsheet, it imports that column in Plain Text. I have defined the receiving field as Long Text with a Rich Text format. I am also using acSpreadsheetTypeExcel12Xml as the Spreadsheet Type. What am I missing?

Is there an alternative to TransferSpreadsheet that will work?

Thanks, Eddie

[June7]

I created table and set field as Long Text and Rich Text. Then I used Import Wizard to append records. The HTML code is recognized.

Then I tested TransferSpreadsheet. Same result.

If you want to provide your Excel file, follow instructions at bottom of my post.

[EddieN1]

June7, thanks for the prompt reply. Can you show me the parameters you used for the TransferSpreadsheet? Also, the table that I am importing into is a simple, local table in Access.

[June7]

Imported to local Access table.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Condos", "my file path", True

[EddieN1]

Update... my problem might be in the Excel spreadsheet. I'm attaching it to this post. Any help would be appreciated... Eddie

[June7]

There is no HTML code in the sheet. This is just Excel formatting. No idea how Excel codes cell formatting. Just as in Access, normal formatting is not with HTML tags. Rich Text uses HTML tags.

My test was with text in Excel cell using HTML tags, not Excel formatting. Excel did not display formatting defined by the tags but Access did after import.

AFAIK, Excel formatting does not export. Can do SaveAs HTML page and then that has HTML tags. Access can import from HTML document but in my test it does not seem to understand the tags. Access recognizes a very limited set of HTML tags.

I added another column to your sheet and entered HTML tagged text. Import fails to recognize the HTML tags. Weird. Now to see if I can even replicate what I did yesterday with a new workbook.

Best I can offer is to restore the line breaks, run an UPDATE query that replaces Chr(10) with Chr(13) & Chr(10).

UPDATE tablename SET fieldname = Replace(fieldname, Chr(10), Chr(13) & Chr(10)) WHERE {some criteria that restricts to new records}.

[Micron]

I read about a trick you can try: change table field to plain text format, do the import, change the field to rtf. OP said that worked BUT they stated that the formatting showed in a form. They didn't say it did so in the table as well. To test that fully, you may need to whip up a form with a textbox formatted as they did (plain text) then alter both the table field and the form control. If it works, it would be a start. Then you'd have to figure out what you would be willing to do to accommodate. One way might be to open the form in design view, alter the control then switch to form view but don't save the form design. I'd want to open it hidden, but I don't know if it can be hidden in design view as I've never tried.

[isladogs]

I'm not sure I understood all of that but in answer to the last sentence, forms can be hidden in design view