Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

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

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

Here is a screenshot of the Excel

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...