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工作簿来解决问题（我还是想这样做）但是我得到一个提示，因为剪贴板数据相当大，所以这也行不通。

3 个解决方案

#1

Since workbook wb belongs to another application instance, you should use

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

wb.Application.CutCopyMode = False

instead

代替

Application.CutCopyMode = False

where wb.Application returns applications instance which workbook wb belongs to.

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

#2

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 DataObject and 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

#3

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

在尝试ActiveSheet时，Application.CutCopyMode = False对我来说无法清除缓冲区或停止出错。粘贴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”）。复制，其中单元格A1为空或非常小。这将使缓冲区真的很小！轻松修复！也许不完全正确，但它在功能上是正确的。

#1