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

Question

I exporting a database query as Excel and I am getting rows with RTF formatting.

How can I convert these fields into plain text? I've found answers that are pretty old, so I was wondering if anyone knows a way.

Answer 1

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.NETFrameworkcurrentver

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 = "{
tf1ansideflang1033ftnbjuc1 {fonttbl{f0 froman fcharset0 Times New Roman;}{f1 fswiss fcharset0 Segoe UI;}} {colortbl ;
ed255green255lue255 ;} {stylesheet{fs22cf0cb1 Normal;}{cs1cf0cb1 Default Paragraph Font;}} paperw12240paperh15840margl1440margr1440margt1440margb1440headery720footery720deftab720formshadeaendnotesaftnnrlcpgbrdrheadpgbrdrfoot sectdpgwsxn12240pghsxn15840marglsxn1440margrsxn1440margtsxn1440margbsxn1440headery720footery720sbkpagepgnstarts1pgncontpgndec plainplainf1fs22lang1033f1 hello question stemplainf1fs22par}"

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.