Excel VBA：无需复制单元格，将含换行的多单元格内容写入剪贴板

要实现不用临时单元格就能生成和CopyPasteWorks完全一致的剪贴板效果，核心是模拟Excel原生复制时的剪贴文本格式：

• 不同单元格之间用**制表符（vbTab）**分隔
• 单元格内的Alt+Enter换行用**换行符（vbLf/Chr(10)）**表示

以下是两种可行的实现方案：

方案1：改进MSForms.DataObject用法 #

直接构造符合格式的字符串，用MSForms.DataObject写入剪贴板：

Sub ClipboardNowWorks_UsingDataObject()
    Dim clipText As String
    ' 构造模拟多单元格的字符串：单元格1含换行，单元格2为普通文本
    clipText = "第一行" & vbLf & "第二行" & vbTab & "普通单元格内容"
    
    Dim dataObj As New MSForms.DataObject
    dataObj.SetText clipText
    dataObj.PutInClipboard
End Sub

方案2：使用Windows API精准控制剪贴板 #

如果MSForms.DataObject存在兼容性问题，可使用API直接写入Unicode文本格式的剪贴板内容：

' 声明API函数
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal uFlags As Long, ByVal dwBytes As Long) As LongPtr
Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As Long
Private Declare PtrSafe Function lstrcpy Lib "kernel32" Alias "lstrcpyW" (ByVal lpString1 As LongPtr, ByVal lpString2 As LongPtr) As LongPtr

Const CF_UNICODETEXT = 13
Const GMEM_MOVEABLE = &H2
Const GMEM_ZEROINIT = &H40

Sub ClipboardNowWorks_UsingAPI()
    Dim clipText As String
    clipText = "第一行" & vbLf & "第二行" & vbTab & "普通单元格内容"
    
    Dim hGlobal As LongPtr, lpGlobal As LongPtr
    Dim clipLength As Long
    
    clipLength = Len(clipText) * 2 + 2 ' Unicode字符串字节数+终止符
    
    ' 打开剪贴板并清空
    If OpenClipboard(0) = 0 Then Exit Sub
    EmptyClipboard
    
    ' 分配全局内存并写入字符串
    hGlobal = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, clipLength)
    lpGlobal = GlobalLock(hGlobal)
    lstrcpy lpGlobal, StrPtr(clipText)
    GlobalUnlock hGlobal
    
    ' 设置剪贴板数据并关闭
    SetClipboardData CF_UNICODETEXT, hGlobal
    CloseClipboard
End Sub

验证效果 #

运行上述任一子过程后，在Excel中粘贴，会得到和CopyPasteWorks完全一致的结果：

• 第一个单元格包含两行内容（Alt+Enter换行效果）
• 第二个单元格为普通文本，和第一个单元格分列显示

内容的提问来源于stack exchange，提问作者HolzwaMi