Excel VBA - 如何清除另一个应用程序中另一个工作簿上的剪贴板？

Excel VBA - How do I clear the clipboard on another workbook in another application?

提问by Petay87

Background:

背景：

I have a script that is formatting Raw Data and appending it to the end of an analysis workbook I have open. The script runs from the analysis workbook as the RAW Data is populated each time.

我有一个脚本，用于格式化原始数据并将其附加到我打开的分析工作簿的末尾。该脚本从分析工作簿中运行，因为每次都填充 RAW 数据。

Issue:

问题：

The script works fine with one exception, I am unable to clear the clipboard on the other workbook and I suspect that this is due to it being open in another instance(Application) of Excel.

该脚本工作正常，但有一个例外，我无法清除另一个工作簿上的剪贴板，我怀疑这是因为它在 Excel 的另一个实例（应用程序）中打开。

My Code So Far:

我的代码到目前为止：

Sub Data_Ready_For_Transfer()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rnglog As Range
    Dim lastrow As Range
    Dim logrange As Range
    Dim vlastrow As Range
    Dim vlastcol As Range
    Dim copydata As Range
    Dim pastecell As Range
    Dim callno As Range

    Set wb = GetObject("Book1")
    Set ws = wb.Worksheets("Sheet1")

    Application.ScreenUpdating = False

    'if we get workbook instance then
    If Not wb Is Nothing Then
        With wb.Worksheets("Sheet1")
            DisplayAlerts = False
            ScreenUpdating = False
            .Cells.RowHeight = 15
            Set rnglog = wb.Worksheets("Sheet1").Range("1:1").Find(What:="Log Date", LookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
            Set lastrow = rnglog.EntireColumn.Find(What:="*", LookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            Set logrange = wb.Worksheets("Sheet1").Range(rnglog, lastrow)
            rnglog.EntireColumn.Offset(0, 1).Insert
            rnglog.EntireColumn.Offset(0, 1).Insert
            rnglog.EntireColumn.Offset(0, 1).Insert
            rnglog.EntireColumn.TextToColumns Destination:=rnglog, DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
                :=Array(Array(1, 1), Array(2, 1), Array(3, 9)), TrailingMinusNumbers:=True
            rnglog.Value = "Log Date"
            rnglog.Offset(0, 1).Value = "Time"
            logrange.Offset(0, 2).FormulaR1C1 = "=WEEKNUM(RC[-2])"
            logrange.Offset(0, 2).EntireColumn.NumberFormat = "General"
            rnglog.Offset(0, 2).Value = "Week Number"
            logrange.Offset(0, 3).FormulaR1C1 = "=TEXT(RC[-3],""mmmm"")"
            logrange.Offset(0, 3).EntireColumn.NumberFormat = "General"
            rnglog.Offset(0, 3).Value = "Month"
            Set vlastrow = wb.Worksheets("Sheet1").Range("A:A").Find(What:="*", LookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            Set vlastcol = vlastrow.EntireRow.Find(What:="*", LookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
            Set copydata = .Range("A2", vlastcol)
            copydata.Copy
        End With
        With ActiveWorkbook.Worksheets("RAW Data")
            Set pastecell = .Range("A:A").Find(What:="*", LookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            Set callno = .Range("1:1").Find(What:="Call No", LookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
            pastecell.Offset(1, 0).PasteSpecial xlPasteValues
            .Cells.RemoveDuplicates Columns:=5, Header:=xlYes
            Application.CutCopyMode = False
        End With
        wb.Close False
        Application.ScreenUpdating = True
        MsgBox "Done"
    End If
End Sub

I thought I would get round the issue by closing the RAW Data workbook (I want to do this anyway) but I get a prompt as the clipboard data is rather large so this also doesn't work.

我以为我会通过关闭 RAW Data 工作簿来解决这个问题（无论如何我都想这样做），但是我收到提示，因为剪贴板数据相当大，所以这也不起作用。

回答by Dmitry Pavliv

Since workbook wbbelongs to anotherapplication instance, you should use

由于工作簿wb属于另一个应用程序实例，您应该使用

wb.Application.CutCopyMode = False

instead

反而

Application.CutCopyMode = False

where wb.Applicationreturns applications instance which workbook wbbelongs to.

其中wb.Application返回工作簿wb所属的应用程序实例。

回答by AKS

What I do is just copy any blank cell in my ActiveWorkbook once I have pasted the values that I copied earlier and I don't need them any more - and this replaces the large data in the Clipboard with an empty string (comparatively nothing) and allows me to close the workbook when I need to.

我所做的只是在粘贴我之前复制的值后复制 ActiveWorkbook 中的任何空白单元格，并且不再需要它们 - 这将剪贴板中的大数据替换为空字符串（相对没有）和允许我在需要时关闭工作簿。

I understand this is rather a workaround, but it works all the time.

我知道这是一种解决方法，但它始终有效。

Another Solution

另一个解决方案

What you should try is to get the MSForms DataObjectand try to put it in clipboard

您应该尝试获取MSForms DataObject并尝试将其放入剪贴板

Dim clipboard As MSForms.DataObject
Set clipboard = New MSForms.DataObject

and then clear it as following:

然后按如下方式清除它：

clipboard.Clear

And if this doesn't work, you can always set the clipboard text to empty

如果这不起作用，您始终可以将剪贴板文本设置为空

clipboard.SetText ""
clipboard.PutInClipboard

回答by user12295

The Application.CutCopyMode = Falsedid not work for me to clear the buffer or stop getting an error when trying ActiveSheet. Paste errorActiveSheet. Paste error

在Application.CutCopyMode = False没有工作对我来说，清除缓冲区或停止尝试时得到一个错误ActiveSheet。粘贴errorActiveSheet。粘贴错误

To clear a large buffer, which is producing an ActiveSheet.Paste error for example is to simply copy an empty cell, for example Range("A1").Copy, where cell A1 would be empty or very small. This will then make the buffer really small! Easy fix! Maybe not precisely correct, but it is functionally correct.

例如，要清除产生ActiveSheet.Paste 错误的大缓冲区，只需复制一个空单元格，例如Range("A1").Copy，单元格 A1 将为空或非常小。这将使缓冲区非常小！轻松修复！也许不完全正确，但它在功能上是正确的。