从 Excel 单元格中的格式化文本中删除标签

发布于 2024-11-05 06:06:54 字数 1996 浏览 1 评论 0原文

和我一起走一会儿吧。

我构建了一个 Access 应用程序来管理公司内部项目的数据。该应用程序的功能之一是查询数据库，然后将查询输出到 Excel 电子表格，然后将电子表格格式化为规范。

输出的单元格之一是来自数据库中富文本备忘录字段的大量文本。当富文本发送到 Excel 时，它会附带指示粗体或斜体的 HTML 标记，因此对于输出，我必须添加格式并删除标记。

这是我需要格式化的文本的示例（该文本位于单个单元格中）：

For each participant, record 1 effort per lesson delivered

   • Time Spent = # minutes spent on lesson

<strong>OR</strong>
For each participant, record 1 effort per month

   • Time Spent = total # minutes spent on lessons that month

    <strong>Note:</strong> Recording 1 effort per lesson is recommended but not     required
<strong>Note:</strong> Use groups function in ABC when appropriate (see <u>Working With     Groups</u> in ABC document library on the ABC portal)

我有三个简洁的小递归函数用于格式化文本，这是粗体函数：

Function BoldCharacters(rng As Range, Optional ByVal chrStart As Long)
'This will find all the "<strong></strong>" tags and bold the text in between.
Dim tagL As Integer
tagL = 8
rng.Select

If chrStart = 0 Then chrStart = 1

b1 = InStr(chrStart, ActiveCell.Value, "<strong>") + tagL
    If b1 = tagL Then Exit Function
b2 = InStr(b1, ActiveCell.Value, "</strong>")

ActiveCell.Characters(Start:=b1, Length:=b2 - b1).Font.Bold = True

'Remove the tags
'ActiveCell.Characters(Start:=1, Length:=1).Delete
'ActiveCell.Characters(Start:=b2 - tagL, Length:=tagL + 1).Delete

'Recursion to get all the bolding done in the cell
Call BoldCharacters(ActiveCell, b2 + tagL + 1)

End Function

现在这就是问题。这很好地格式化了文本。但是，当我尝试使用“ActiveCell.Characters.Delete”方法删除标签时，该方法失败，因为单元格包含超过 255 个字符。所以我不能使用删除方法。

当我这样做时：

With xlApp.Selection
    .Replace what:="<strong>", replacement:=""

标签全部被删除，但格式全部被破坏！那么有什么意义呢！

我正在寻找一种格式化文本并删除标签的方法。我正在考虑采用大量文本并将其“分块”到多个单元格中，处理格式并重新-组装，但这听起来很困难，容易出错，甚至可能不起作用。

有什么想法吗！？

谢谢！

原文

Walk with me for a moment.

I have built an Access application to manage data for an internal project at my company. One of the functions of this application is queries the database, then outputs the queries to an Excel spreadsheet, then formats the spreadsheet to spec.

One of the cells of the output is a large amount of text from a Rich Text Memo field in the database. When the rich text is sent to Excel it carries with it HTML tags indicating bold or italic, so for the output I have to add the formatting and remove the tags.

Here is an example of the text I need to format (this text is in a single cell):

For each participant, record 1 effort per lesson delivered

   • Time Spent = # minutes spent on lesson

<strong>OR</strong>
For each participant, record 1 effort per month

   • Time Spent = total # minutes spent on lessons that month

    <strong>Note:</strong> Recording 1 effort per lesson is recommended but not     required
<strong>Note:</strong> Use groups function in ABC when appropriate (see <u>Working With     Groups</u> in ABC document library on the ABC portal)

I have a three neat little recursive functions for formatting the text, here is the bolding function:

Function BoldCharacters(rng As Range, Optional ByVal chrStart As Long)
'This will find all the "<strong></strong>" tags and bold the text in between.
Dim tagL As Integer
tagL = 8
rng.Select

If chrStart = 0 Then chrStart = 1

b1 = InStr(chrStart, ActiveCell.Value, "<strong>") + tagL
    If b1 = tagL Then Exit Function
b2 = InStr(b1, ActiveCell.Value, "</strong>")

ActiveCell.Characters(Start:=b1, Length:=b2 - b1).Font.Bold = True

'Remove the tags
'ActiveCell.Characters(Start:=1, Length:=1).Delete
'ActiveCell.Characters(Start:=b2 - tagL, Length:=tagL + 1).Delete

'Recursion to get all the bolding done in the cell
Call BoldCharacters(ActiveCell, b2 + tagL + 1)

End Function

Now here's the issue. This formats the text nicely. But the "ActiveCell.Characters.Delete" method fails when I attempt to use it to remove the tags because the cell contains more than 255 characters. So I can't use the delete method.

And when I do this:

With xlApp.Selection
    .Replace what:="<strong>", replacement:=""

The tags are all removed, but the formatting is all destroyed! So what's the point!?

I'm looking for a way of formatting my text and removing the tags. I'm considering taking the large bit of text and 'chunking' it up into a number of cells, processing the formatting and re-assembling, but that sounds difficult, prone to error, and might not even work.

Any ideas!?

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧城烟雨 2024-11-12 06:06:54

在将数据导出到 Excel 之前，您可能需要删除格式设置。在删除格式的同时，将格式信息（位置、长度、样式）存储到数据结构中。导出“纯文本”数据后，您可以迭代结构并在 Excel 中应用格式。这可能是一个耗时的过程，具体取决于您计划在给定时间导出的记录数量，但它将消除 Excel 施加的限制。

回复收藏 0 原文

旧梦荧光笔 2024-11-12 06:06:54

如果它是格式良好的 html（即它总是有结束标签），那么您可以使用正则表达式。

Dim data As String
data = "For each participant, record 1 effort per lesson delivered     • Time Spent = # minutes spent on lesson  <strong>OR</strong> For each participant, record 1 effort per month     • Time Spent = total # minutes spent on lessons that month      <strong>Note:</strong> Recording 1 effort per lesson is recommended but not     required <strong>Note:</strong> Use groups function in ABC when appropriate (see <u>Working With     Groups</u> in ABC document library on the ABC portal)"
Dim r As New RegExp
r.Pattern = "<(.|\n)*?>"
r.Global = True
Debug.Print r.Replace(data, "")

要使用 RegExp 对象，请设置对 Microsoft VBScript Regular Expressions 5.5 的引用。

本

If it's well formed html (ie it always has closing tags) then you could use a regular expression.

Dim data As String
data = "For each participant, record 1 effort per lesson delivered     • Time Spent = # minutes spent on lesson  <strong>OR</strong> For each participant, record 1 effort per month     • Time Spent = total # minutes spent on lessons that month      <strong>Note:</strong> Recording 1 effort per lesson is recommended but not     required <strong>Note:</strong> Use groups function in ABC when appropriate (see <u>Working With     Groups</u> in ABC document library on the ABC portal)"
Dim r As New RegExp
r.Pattern = "<(.|\n)*?>"
r.Global = True
Debug.Print r.Replace(data, "")

To use the RegExp object, set a reference to Microsoft VBScript Regular Expressions 5.5.

hth

Ben

回复收藏 0 原文

谈场末日恋爱 2024-11-12 06:06:54

这些内容可能有用：

Sub DoFormat(rng As Range)
    Dim DataObj As New MSForms.DataObject
    Dim s As String, c As Range

    For Each c In rng.Cells
        s = "<html>" & Replace(c.Value, " ", " ") & "</html>"
        DataObj.SetText s
        DataObj.PutInClipboard
        c.Parent.Paste Destination:=c
    Next c

End Sub

您需要引用“Microsoft Forms 2.0 Object Library”

Something along these lines might be useful:

Sub DoFormat(rng As Range)
    Dim DataObj As New MSForms.DataObject
    Dim s As String, c As Range

    For Each c In rng.Cells
        s = "<html>" & Replace(c.Value, " ", " ") & "</html>"
        DataObj.SetText s
        DataObj.PutInClipboard
        c.Parent.Paste Destination:=c
    Next c

End Sub

You'll need a reference to "Microsoft Forms 2.0 Object Library"

回复收藏 0 原文

~没有更多了~