vba 在 Excel 中将 RTF（富文本格式）代码转换为纯文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42576291/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert RTF (Rich Text Format) code into plain text in Excel
提问by Ivan
I exporting a database query as Excel and I am getting rows with RTF formatting.
我将数据库查询导出为 Excel，并且我正在获取具有 RTF 格式的行。
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.
如何将这些字段转换为纯文本？我找到了很老的答案，所以我想知道是否有人知道方法。
回答by Slai
Another alternative can be using Microsoft Rich Textbox Control (but can't test it on x64 Office)
另一种选择是使用 Microsoft Rich Textbox Control（但不能在 x64 Office 上测试）
Sub rtfToText()
With CreateObject("RICHTEXT.RichtextCtrl") ' or add reference to Microsoft Rich Textbox Control for early binding and With New RichTextLib.RichTextBox
.SelStart = 0 ' needs to be selected
.TextRTF = Join(Application.Transpose(Cells.CurrentRegion.Columns(1)))
[C1] = .Text ' set the destination cell here
' or if you want them in separate cells:
a = Split(.Text, vbNewLine)
Range("C3").Resize(UBound(a) + 1) = Application.Transpose(a)
End With
End Sub
回答by Robert Thompson
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.
.Net Framework RichTextBox 类可以执行转换。幸运的是，这个类设置了 ComVisibleAttribute，因此可以从 VBA 中轻松使用它。
I had to create a .tlb file to Reference. In the
我必须创建一个 .tlb 文件来引用。在里面
%SYSTEMROOT%\Microsoft.NET\Framework\currentver\
%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.
创建 system.windows.forms.tlb 文件。我的系统上已经有了这个 .tlb 文件，但我必须使用这个命令重新创建它才能在 VBA 中成功创建一个 .Net System.Windows.Forms RichTextBox 对象。
With the new .tlb file created, in VBA link it to your project via Tools->References in the VBA IDE.
创建新的 .tlb 文件后，在 VBA 中通过 VBA IDE 中的 Tools->References 将其链接到您的项目。
I wrote this test code in Access to demonstrate the solution.
我在 Access 中编写了此测试代码来演示解决方案。
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
结果
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.
我假设在带有 64 位 Office 的 64 位 Windows 上需要在 \Framework64\ 目录中重新创建 .tlb 文件。我在 32 位 Office 2013 上运行 64 位 Win10，所以我必须有一个 32 位 .tlb 文件。
