如何使用VBA代码将带有项目符号，粗体字符和分隔线的文本粘贴到单个Excel单元格中？

I want to do the equivalent of selecting a cell in a sheet, pressing F2 and then CTRL+V, so that all the formatting such as bullets, bold characters and breaklines are preserved and pasted into one cell. Please note that I'm NOT copying from excel sheets, rather im using excel vba to copy from a Word Document.

我想做相当于在工作表中选择单元格,按F2然后按CTRL + V,以便保留所有格式,如项目符号,粗体字符和分隔线,并粘贴到一个单元格中。请注意,我不是从excel表复制,而是使用excel vba从Word文档复制。

I've tried:

Sheets(1).Cells(1,1).Select
SendKeys "{F2}", True
SendKeys "v", True
Application.Wait(Now + TimeValue("0:00:5"))
Sheets(1).Cells(1,2).Select

But the above code pastes in the cell(1,2) of the active sheet instead of (1,1). Please suggest an alternative method for this.

但是上面的代码粘贴在活动工作表的单元格(1,2)中而不是(1,1)。请为此建议一种替代方法。

2 个解决方案

#1

To copy and paste a cell "preserving the formatting" (ie bold, underlines, text column, newlines, special characters (like bullet points) etc. You could use record macro to create code like the following which is simple copy, paste. You do not need to press F2 (as this will only give you access to the raw value - not the formatting):

复制并粘贴单元格“保留格式”(即粗体,下划线,文本列,换行符,特殊字符(如项目符号点)等)您可以使用记录宏来创建如下的简单复制,粘贴等代码。不需要按F2(因为这只会让您访问原始值 - 而不是格式化):

Range("C3").Select
Selection.Copy
Range("D6").Select
ActiveSheet.Paste

modifying this example for you needs:

根据您的需要修改此示例:

With Sheets(1)
    .Cells(1, 1).Copy
    .Cells(1, 2).Select
    .Paste
end with 

#2

SendKeys "^v", True

This will send Ctrl+v. You shouldn't need the Wait command because the {True} argument for {SendKeys} tells Excel to wait until it's done before returning continuing with the macro. I'm not sure if there's a better way to do this that doesn't involve SendKeys, unless you want to paste into multiple cells and then combine them.

这将发送Ctrl + v。您不应该需要Wait命令,因为{SendKeys}的{True}参数告诉Excel等到它完成后再继续使用宏。我不确定是否有更好的方法来执行此操作,不涉及SendKeys,除非您想要粘贴到多个单元格然后组合它们。