如何使用富文本格式复制Excel单元格而不是复制全局单元格格式？ [英] How can I copy Excel cells with rich text formatting but not the global cell format?

问题描述

使用VBA，我将一个单元格的值复制到另一个单元格中：

  Dim s As Range 
 Dim d As Range 
 Set s = Range（A1）
设置d =范围（B2）
 d.Value = s.Value 
  

这工作正常，但如果源文件包含丰富的文本格式，则格式化将丢失。

  s.Copy d 
  

但是，这不仅带来了丰富的文本格式，还带来了细胞上的全局格式 - 背景颜色，边框等等。我只想复制适用于部分的格式（例如，粗体中的一个字）。

我也尝试复制每个字符的格式：

 对于ci = 1到Len（sourcevalue） 
 d.Characters（ci，1）.Font.Bold = s.Characters（ci，1）.Font.Bold 
下一个
  

上面的实际代码ve包括斜体，下划线等，并将Characters（）缓存为对象的速度，但仍然表现为生产使用的速度太慢。

我可以想到的最后一个选项是用格式复制单元格，然后撤消对背景颜色或图案，边框，字体名称/大小等的任何更改:

  bg = d.Interior.ColorIndex 
 s.Copy d 
 d.Interior.ColorIndex = bg 
  

这似乎还是kludgy，很难节省要重新应用的所有格式，我可以' t撤消格式化，如粗体，斜体等，可以在单元格或字符级别应用，而不会删除字符级设置。

任何其他选项那里？ Excel OpenOfficeXML文件中的格式存储有字符范围，但不会出现这些格式化的范围通过API可用，至少我可以找到。

编辑：使用下面的KazJaw的方法，我可以使用以下代码获取所需的内容：

  Dim TmpFormat As Range 
 Set TmpFormat = Range（$ XFD $ 1）
 Dest.Copy 
 TmpFormat.PasteSpecial xlPasteFormats 
 Source.Copy 
 Dest .PasteSpecial xlPasteAll 
 TmpFormat.Copy 
 Dest.PasteSpecial xlPasteFormats 
 Dest.Font.Name = TmpFormat.Font.Name 
 Dest.Font.Size = TmpFormat.Font.Size 
 TmpFormat.ClearFormats 
  

这将暂时保留我的目标单元格的单元格格式第一行，使用所有格式复制源，粘贴返回在temp单元格中的格式，最后还会复制保留的整体字体和大小（在我的情况下我不想从源代码复制）。最后，临时单元格被清除，因此它不会影响工作表维度。

这是一个不完美的解决方案，特别是因为它依赖于剪贴板，但是性能很好并且它做了它需要做的。

解决方案

我刚刚做了一些测试，我对结果感到惊讶可能是您的解决方案，或者只是一个提示来探索更多。

想象一下，我们开始时有这样的情况：

如果你运行这个简单的代码：...

  Sub PossibleSolution（）
 
范围（A1）复制'格式化的单元格
范围（A3）PasteSpecial xlPasteAll 
范围（A2）复制'清除格式单元格
范围（A3）PasteSpecial xlPasteFormats 
 
 End Sub 
  

...您将得到以下结果：

Using VBA, I'm copying the value of one cell to another:

Dim s As Range
Dim d As Range
Set s = Range("A1")
Set d = Range("B2")
d.Value = s.Value

This works fine, but if the source contains rich text formatting, the formatting is lost.

I could copy the entire cell, formats and all:

s.Copy d

But this brings along not just rich text formatting, but also global formats on the cell -- background color, borders, etc. I'm only interested in copying formats that apply to portions of the text (for example, one word in a sentence being in bold).

I've also tried copying the format of each character:

For ci = 1 to Len(sourcevalue)
   d.Characters(ci, 1).Font.Bold = s.Characters(ci, 1).Font.Bold
Next

The actual code for the above includes italics, underlines, etc., and caches Characters() as an object for speed, but still, the performance is way too slow for production use.

The last option I can think of is to copy the cell with formatting, then undo any changes to background color or pattern, borders, font name/size, etc.:

bg = d.Interior.ColorIndex
s.Copy d
d.Interior.ColorIndex = bg

This still seems kludgy, it's difficult to save off all of the formats to be re-applied, and I can't "undo" formatting like bold, italics, etc. that could either be applied at a cell or character level without erasing the character-level settings.

Any other options out there? The formatting in the Excel OpenOfficeXML file is stored with ranges of characters, but it doesn't appear these formatted ranges are available via the API, at least as far as I can find.

Edit: Using KazJaw's approach below, I was able to get what I needed with the following code:

Dim TmpFormat As Range
Set TmpFormat = Range("$XFD$1")
Dest.Copy
TmpFormat.PasteSpecial xlPasteFormats
Source.Copy
Dest.PasteSpecial xlPasteAll
TmpFormat.Copy
Dest.PasteSpecial xlPasteFormats
Dest.Font.Name = TmpFormat.Font.Name
Dest.Font.Size = TmpFormat.Font.Size
TmpFormat.ClearFormats

This temporarily preserves the cell formatting of my destination cell in the last cell of the first row, copies the source with all formatting, pastes back the formatting in the temp cell, and finally also copies back the preserved overall font and size (which in my case I don't want to copy over from the source). Finally, the temp cell is cleared so it doesn't impact the worksheet dimensions.

It's an imperfect solution, especially since it relies on the clipboard, but the performance is good and it does what it needs to do.

解决方案

I've just made some test and I'm bit surprised with results which could be solution for you or just a tip to explore it more.

Imagine we have this situation at the beginning:

If you run this simple code:...

Sub PossibleSolution()

    Range("A1").Copy     'full formatted cell
    Range("A3").PasteSpecial xlPasteAll
    Range("A2").Copy     'clear format cell
    Range("A3").PasteSpecial xlPasteFormats

End Sub

...you will get the following result:

这篇关于如何使用富文本格式复制Excel单元格而不是复制全局单元格格式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！