Replace html / RTF Tags in Excel with Formatted Text

[tyjamesbren]

Hello all,

I'm new to this server but I've been working on excel VBA coding for a bit of time. I have made a pretty useful code that I want to share with the community.

BIG NOTE: For some reason, I couldn't post this with the html tags, which is the whole purpose of this code. To make this code work, you'll need to replace "{" with "<" and replace "}" with ">". Sorry for the inconvenience.

Here is the gist; I work with databases often, ms access in particular. I have encountered a problem before where I try to export Rich Text Format (RTF) into excel, but none of the formatting is retained. When you export directly from access, it may look like the sample below:
{div}Hello {strong}{em}world{/em}{/strong}. I say &quot;Hi&quot;.{/div}
With RTF this should appear this way:
Hello world. I say "Hi".

To apply this formatting to cells with less than 255 characters, a simple Range.Characters(i,j).Insert or Range.Characters(i,j).Delete method paired with a Range.Characters(i,j).Font.Bold would work to apply the formatting where the html tag for Bold ({strong}) appears. However, with RTFs this is usually not the case. So, I have developed the code below which can perform the same operation for cells with text longer than 255 characters:

Please Login or Register  to view this content.

Note: this code will work for the selected cells only.

This code tackles each cell at a time, for this reason the operation may take longer for very large workbooks.

The code works by first replacing the text codes which do not need any formatting applied to them; things like {div} are replaced with nothing, &quot; is replaced with ", and &nbsp; is replaced with two blank spaces. I have additional coding to remove duplicated carriage returns / line feeds which access seems to add for no reason. Also, I didn't bother to apply different font formatting here, for my purposes this is unnecessary.

Next, the code inventories the now semi-stripped text for occurrences of html formatting codes. To get an accurate number of the position of the formatting code, all other formatting codes must be removed before inventorying the text. The position and length of the text to be formatted is stored in an array for each formatting style.

Finally, the code strips the remaining formatting codes and cycles through each array to apply the different formatting styles. Before moving on to the next cell, each array is cleared.

I believe this code should work very well for almost all MS Access formatting options (MS Access RTF is relegated to a short list of 10-15 formatting options as opposed to other html RTF applications). To expand on this code for your own purposes you can follow the following steps:

Please Login or Register  to view this content.

[/INDENT][/LIST]
If anyone has improvements to this code, suggestions, or questions please let me know. This made my life a lot easier, I hope it can help you out too.

[alansidman]

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[tyjamesbren]

Thanks for the help.