Exporting Rich Text To Excel

RichTextToExcel

The Problem

Every once in a while I will come across a user who wants to export rich text (Feature added in 2007) to excel. If you have ever tried automating Excel from Access you will know how great the interaction between the office applications can be. But sadly in this instance it fails horribly. You cannot export the rich text to excel. At best, you can strip the formatting and export the plaintext to excel. This can be done by using the built-in PlainText() function.

The Consideration.

Anyway, I finally decided to give it a shot. After all, one of the things I’ve always liked most about Access is how the programmers who came before me have managed to make Access do things it wasn’t designed for. Stephan Lebans excellent code library comes to mind, and I have used is samples many times.

The Challenge

So I set myself the challenge of creating a function that could export Access Rich Text To Excel. Now little did I know that this was actually a lot more difficult than I first had imagined. The html formatting of a rich text field can be overlapping, and I needed to get the character position of each formatting type individually. But I needed the position of the formatted character in the plaintext string, not the html string. So in the end I actually needed to de-construct the entire html into bits and pieces, store it as a string and a format array, THEN re-assemble the formatting in Excel, because basically Excel speaks a different kind of Rich text than Access does. This also means that there are actually some Access Rich Text features that doesn’t export or transfer to Excel, e.g. bullets lists, indenting and highlighting. I’ve made sure the code ignores these, so that you won’t get an error message.

 

 The Solution

I managed to make the code that will export Rich text to excel (At least the parts that are supported). I’ve wrapped it in a function called CopyRecordsetToExcel which will optionally also include field headers. The full code and demo is available in this download:

 

RichTextToExcel

and I’ve also added a demo form that shows the creation of an excel object through late bound automation. To use the function, pass in the recordset and the top left cell of the excel range which you want to export too.

 

Contest

The first person to report to me a piece of Access rich text that doesn’t get exported properly (And which is supported by Excel’s Rich text) will get a 25€ discount on my Access Europe Seminar, June 2nd in Copenhagen.

Advertisements
12 comments on “Exporting Rich Text To Excel
  1. Kon Samulis says:

    (1) Thanks for this code – it is very helpful…

    I have found an oddity, in that in a table, the Rich Text font color is being stored as and not a HEX value for the color….the code then fails as it expects the HEX value and not text…. As a fix, I am replacing all the colour names that I am using with their Hex value after it has arrived in Excel…. Oh, and thanks for opening up my eyes to “CopyFromRecordset” – it does not truncate at 256 characters!!! (especially for loooong Rich Text slabs…)

    • TheSmileyCoder says:

      Hi there
      Thanks for the feedback. Could you maybe send me or post here some an example of some Access Rich text that does not export well? Then I will look into converting it.

  2. Terry says:

    Hi Smiley, I have cut and pasted some rtf into the example Access db, and then run the export. However, the Excel spreadsheet does not display the text as rtf – it includes all the rtf codes as though it was pasted into Notepad.
    Your comments seem to indicate the export would handle html – does it also handle rtf?

    • Terry says:

      sorry: I should have pasted the rtf:

      {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Arial;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
      {\colortbl ;\red0\green0\blue255;}
      \viewkind4\uc1\pard\lang3081\f0\fs20 There is no erosive or degenerative\cf1\revised change\cf0\revised0 around either hip joint\lang1033\f1\fs17\par
      }

    • TheSmileyCoder says:

      Hi Terry.
      Access rich text is stored in an html “like” syntax, and it is that HTML that I parse, export, and reformat. It doesn’t do anything at all with RTF formatted text, sorry.

  3. Greg Sevior says:

    Hi. Great solution, however including bold and coloured text in the same field for outputting breaks the code, with a Type mismatch when it comes to dealing with the colored text. It appears to be an issues with the stripping of the tag stripping which occurs.

    • TheSmileyCoder says:

      Thank you for reporting this. I admit I haven’t looked at this code in years, and don’t currently have the time to look into fixing it.

  4. Ben Sacheri says:

    This does not handle font colors if they are named instead of being in Hex format. For example, “” causes GetFontColor() to fail. I have modified this function to handle the 16 most common HTML color names.

    Public Function GetFontColor(sFont As String) As Variant
    Dim sColor As String
    If InStr(1, sFont, “Color=”) = 0 Then
    Exit Function
    Else
    If InStr(1, sFont, “#”) > 0 Then
    ‘ Color stored as Hex
    sColor = Mid(sFont, InStr(1, sFont, “Color=”) + 8, 6)
    GetFontColor = RGB(“&H” & Mid(sColor, 1, 2), “&H” & Mid(sColor, 3, 2), “&H” & Mid(sColor, 5, 2))
    Else
    ‘ Color stored as a word.
    ‘ Get the RGB code for these 16 common colors. https://htmlcolorcodes.com
    sColor = Mid(sFont, InStr(1, sFont, “Color=”) + 6)
    sColor = Left(sColor, Len(sColor) – 1)
    If sColor = “WHITE” Then
    GetFontColor = RGB(255, 255, 255)
    ElseIf sColor = “SILVER” Then
    GetFontColor = RGB(192, 192, 192)
    ElseIf sColor = “GRAY” Then
    GetFontColor = RGB(128, 128, 128)
    ElseIf sColor = “BLACK” Then
    GetFontColor = RGB(0, 0, 0)
    ElseIf sColor = “RED” Then
    GetFontColor = RGB(255, 0, 0)
    ElseIf sColor = “MAROON” Then
    GetFontColor = RGB(128, 0, 0)
    ElseIf sColor = “YELLOW” Then
    GetFontColor = RGB(255, 255, 0)
    ElseIf sColor = “OLIVE” Then
    GetFontColor = RGB(128, 128, 0)
    ElseIf sColor = “LIME” Then
    GetFontColor = RGB(0, 255, 0)
    ElseIf sColor = “GREEN” Then
    GetFontColor = RGB(0, 128, 0)
    ElseIf sColor = “AQUA” Then
    GetFontColor = RGB(0, 255, 255)
    ElseIf sColor = “TEAL” Then
    GetFontColor = RGB(0, 128, 128)
    ElseIf sColor = “BLUE” Then
    GetFontColor = RGB(0, 0, 255)
    ElseIf sColor = “NAVY” Then
    GetFontColor = RGB(0, 0, 128)
    ElseIf sColor = “FUCHSIA” Then
    GetFontColor = RGB(255, 0, 255)
    ElseIf sColor = “PURPLE” Then
    GetFontColor = RGB(128, 0, 128)
    Else
    ‘ For colors we don’t know how to convert change them to black;
    ‘ Or you can assign another color to make it stand out so it can be corrected.
    GetFontColor = RGB(0, 0, 0)
    End If
    End If
    End If
    End Function

  5. Ben Sacheri says:

    Doh! My example was stripped out of my last post because it was HTML. Here it is with different braces: {font color=black}

  6. Ben Sacheri says:

    @TheSmileyCoder this is a very helpful routine since nothing else like it exists. Here are the caveats that I found that future users should take note of.

    1. Does not handle colors written as words: {font color=black} FIXED! (see above)
    2. The top left most cell range you allow in Excel is B2. A1 fails.
    3. Does not handle hyperlinks. Routine fails.
    4. Does not handle background color like this: {font color=black style=”BACKGROUND-COLOR:#FFFF00″} I realize that Excel doesn’t support a background color on text but Access does so the code should not fail when encountering it.

  7. Abhijeet Parge says:

    Hi guys,

    I’m really new to excel and just wondering if someone can help me with the following problem:

    I have a “form” / “notes template” created on WORD using rich text and autocorrect options. However, I want to import this into an excel sheet but when I copy paste it, it removes all formatting and turns it into plain text. I was just wondering if I can use this code to import my rich text from word to excel? if so, can someone please break it down for me?

    Thanks guys

    • TheSmileyCoder says:

      Hi
      No, I am afraid that is not possible. Access uses a different rich text format than excel, which is again different from what Word uses. Word probably has the best support for rich text, and there are things in word, that simple doesn’t transfer to excel (and similar for Access->Excel).
      The code here is actually parsing the rich text in access, and converting it to the format that excel supports. You could use the same approach idea, but the code would have to be completely adjusted.

Ask a question, leave some feedback, post a reply!

This site uses Akismet to reduce spam. Learn how your comment data is processed.