Display an MS-Access record's Rich Text Format in Excel

Question

What I want to do

I have an Access database ( C:\Users\289894\Desktop\Database1.accdb ) One of the fields of [Table1] is [Memo].

That field is of the Memo data type, with the text format set to rich text. It makes it possible to save some records as Bold and some records saved as Italics, for example.

I want to open a connexion to this Access database from an excel file in order to read/write into this rich text field.

The code I used is the following:

    Dim datab As Database
    Dim rs As Recordset
    Dim path As String

    path = "C:\Users\289894\Desktop\Database1.accdb"

    Set datab = OpenDatabase(path)
    Set rs = datab.OpenRecordset("SELECT * FROM [Table1]")

        Debug.Print rs!Memo
        Range("A1") = rs!Memo

My question

This code works well to open a connexion and read ordinary text fields, but rich text acts in a surprising way (for me). The original text in access was "aaa". That's "aaa" in bold font.

After running the code, both the debug.print and Range("A1") have <div><strong>aaa</strong></div> written into them.

How can I change my code to send the format to excel as well? I'd like to have "aaa" written in bold in cell A1, just like it is in Access.

EDIT: Workaround This solves the immediate problem asked by the question without really answering the question itself. It uses internet explorer to paste the text back as Rich Text, without the tags.

Sub Sample()
    Dim Ie As Object
    Dim rng As Range

        Set rng = Feuil1.Range("A1")

        Set Ie = CreateObject("InternetExplorer.Application")

        With Ie
            .Visible = False

            .Navigate "about:blank"

            .Document.body.InnerHTML = rng.Value

            .ExecWB 17, 0
            'Select all contents in browser
            .ExecWB 12, 2
            'Copy them
            ActiveSheet.Paste Destination:=rng


            .Quit
        End With

End Sub

This is an interesting question. I don't have a straight up solution in terms of settings or user a different export method, but a hack you could use is to loop the rowset after import and remove the html tags and make the cells bold, or italic, depending on the tag. Also the title seems to have nothing to do with the question. — Scott Holtzman
@ScottHoltzman Good catch on the title, I'm not sure what had happened there. As for pasting back as HTML, I thought about it, but the best answer I found was stackoverflow.com/questions/9999713/… and it throws an error for me (EDIT: It no longer works with newer IE versions). — David G
Did you try this answer from the question you linked? stackoverflow.com/a/14951531/3820271 — Andre
Yes, I also don't have the required library in my list and this code is supposed to run on several machines so importing librairies is not really an option. — David G
I'm fairly certain that every Windows machine has FM20.dll, but it doesn't matter - there are other methods to put a string into the clipboard, e.g. access.mvps.org/access/api/api0049.htm — Andre

Scott Holtzman Scott Holtzman · Accepted Answer · 2016-07-05T17:21:50

Give this a shot. Loop range is generic. Also, function assumes very limited HTML as shown in your example.

Sub Test()

Dim cel As Range
For Each cel In Range("A1:A100")

    cel.Font.Bold = InStr(1, cel.Value, "<strong>")
    cel.Font.Italic = InStr(1, cel.Value, "<i>")
    cel.Value = RemoveHTML(cel.Value)

Next

End Sub

Function RemoveHTML(sHTML As String) As String

Dim sTemp As String
sTemp = sHTML

Dim bLeft As Byte, bRight As Byte
bRight = InStr(1, sTemp, "</")
bLeft = InStrRev(sTemp, ">", bRight)

RemoveHTML = Mid(sTemp, bLeft + 1, bRight - bLeft - 1)

End Function

Display an MS-Access record's Rich Text Format in Excel

1 Answers