Convert RTF (Rich Text Format) code into plain text in Excel

by

The .Net Framework RichTextBox class can perform the conversion. Fortunately, this class has the ComVisibleAttribute set, so it can be used from VBA without much difficulty.

I had to create a .tlb file to Reference. In the

%SYSTEMROOT%\Microsoft.NET\Framework\currentver\

directory, run the command

regasm /codebase system.windows.forms.dll

to create the system.windows.forms.tlb file. I already had this .tlb file on my system, but I had to recreate it using this command to be able to create a .Net System.Windows.Forms RichTextBox object successfully in VBA.

With the new .tlb file created, in VBA link it to your project via Tools->References in the VBA IDE.

I wrote this test code in Access to demonstrate the solution.

Dim rtfSample As String
rtfSample = "{\rtf1\ansi\deflang1033\ftnbj\uc1 {\fonttbl{\f0 \froman \fcharset0 Times New Roman;}{\f1 \fswiss \fcharset0 Segoe UI;}} {\colortbl ;\red255\green255\blue255 ;} {\stylesheet{\fs22\cf0\cb1 Normal;}{\cs1\cf0\cb1 Default Paragraph Font;}} \paperw12240\paperh15840\margl1440\margr1440\margt1440\margb1440\headery720\footery720\deftab720\formshade\aendnotes\aftnnrlc\pgbrdrhead\pgbrdrfoot \sectd\pgwsxn12240\pghsxn15840\marglsxn1440\margrsxn1440\margtsxn1440\margbsxn1440\headery720\footery720\sbkpage\pgnstarts1\pgncont\pgndec \plain\plain\f1\fs22\lang1033\f1 hello question stem\plain\f1\fs22\par}"

Dim miracle As System_Windows_Forms.RichTextBox
Set miracle = New System_Windows_Forms.RichTextBox
With miracle
    .RTF = rtfSample 
    RTFExtractPlainText = .TEXT
End With

MsgBox RTFExtractPlainText(rtfSample)

With the result

hello question stem

I’d assume re-creating the .tlb file in the \Framework64\ directory would be needed on 64-bit Windows with 64-bit Office. I am running 64-bit Win10 with 32-bit Office 2013, so I had to have a 32-bit .tlb file.

More Related Contents:

  1. VBA – How to grab the cell below if a cell has text?
  2. Excel VBA – A general how to
  3. VBA code required for Msg Box when two value equal in two cells
  4. Find text and show value if condition is met
  5. Excel check len string separated by comma [closed]
  6. How can I insert variable into formula in VBA
  7. Excel column number from column name
  8. VBA – destroy a modeless UserForm instance properly
  9. How To Speed Up VBA Code
  10. Execute a command in command prompt using excel VBA
  11. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  12. Copy data from another Workbook through VBA
  13. Excel VBA Load worksheet in Function
  14. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  15. Load csv file into a VBA array rather than Excel Sheet
  16. Insert line break in wrapped cell via code
  17. Change color of certain characters in a cell
  18. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  19. Consolidating worksheets into one
  20. Copy filtered data to another sheet using VBA
  21. Range.Find on a Date That is a Formula
  22. Range() vs Cells() vs Range.Cells()
  23. Add new row to excel Table (VBA)
  24. How do I open a file if I only know part of the file name?
  25. VBA Function argument list select
  26. VBA recognizing workbook by partial name
  27. How to call function from another specific workbook in VBA?
  28. Open Google Chrome from VBA/Excel
  29. Excel VBA Run-time error 1004 when inserting or value formula into cell
  30. How to wait until ActiveWorkbook.RefreshAll finishes before executing more code

Leave a Comment