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.

Posted in Misc., Tip Of The Week
5 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.

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