Exporting Rich Text To Excel
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.
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.
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.
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:
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.
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.