excel - 从 Excel 复制到 Word 文档时出现剪贴板错误

Question

我正在尝试将 Excel 单元格(单元格 A1 到 A66)中的文本复制到 Word 文档中。此操作的目标是将其复制并粘贴为 TEXT。如果直接从 Excel 复制，它将粘贴为表格。

Private Sub Bouton1_Click()
    
    Dim objWord As New Word.Application
    With objWord
        .Documents.Add
        Application.Wait (Now + TimeValue("0:00:01") / 2)
        Worksheets("Description2").Cells(1, 1).Copy
        Application.Wait (Now + TimeValue("0:00:01") / 2)
        .Selection.PasteSpecial xlPasteValues
        .Visible = True
    End With
    
    Dim i As Integer
    For i = 2 To 66
        If Worksheets("Description2").Cells(i, 1) = Worksheets("Description2").Cells(i + 1, 1) Then Exit For
        With objWord
            Application.Wait (Now + TimeValue("0:00:01") / 2)
            Worksheets("Description2").Cells(i, 1).Copy
            Application.Wait (Now + TimeValue("0:00:01") / 2)
            .Selection.PasteSpecial xlPasteValues
            .Visible = True
        End With
    Next i
    
    objWord.Application.Activate
    objWord.Application.WindowState = wdWindowStateMaximize
    
End Sub

此代码大约有 70% 的时间有效。当它不起作用时，我会收到此错误(或变体，但始终与剪贴板有关):

Run-Time error "4605": This method or property is not available
because the clipboard is empty or not valid.

此外，有时会打开一个随机的 OneDrive 窗口。

我已经添加了 Application.Wait 行来尝试减慢复制/粘贴的速度，但效果并不明显。

如何使我的代码更可靠？

Answer 1

如果你想粘贴为文本，也许:

Sub CopyAsTextToWord()
    Dim wordApp As New Word.Application

    With wordApp
        .Visible = True
        .Documents.Add

        Worksheets("Description2").Range("A1:A66").Copy
        .Selection.PasteSpecial DataType:=wdPasteText
    End With
End Sub

另一方面，如果您想一次粘贴每个单元格一个(这是您原始代码的样子，不确定)，也许方法略有不同，避免剪贴板.将范围读入数组，遍历它，然后使用 Selection.TypeText 按顺序“粘贴”每个元素。可能可以变得更健壮。

Sub TransferAsText()
    Dim wordApp As New Word.Application

    With wordApp
        .Visible = True
        .Documents.Add

        Dim arr()
        arr = Worksheets("Description2").Range("A1:A66").Value

        Dim i As Long
        For i = LBound(arr, 1) To UBound(arr, 1)
            .Selection.TypeText Text:=CStr(arr(i, 1))
        Next i
    End With
End Sub