Excel VBA - 如何在另一个应用程序中清除另一个工作簿上的剪贴板？ [英] Excel VBA - How do I clear the clipboard on another workbook in another application?

问题描述

背景：

我有一个格式化原始数据的脚本，并将其附加到我已经打开的分析工作簿的末尾。每次RAW数据填充时，脚本将从分析工作簿运行。

问题：

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

我的代码远远：

  Sub Data_Ready_For_Transfer（）
 Dim wb As Workbook 
 Dim ws As Worksheet 
 Dim rnglog As Range 
 Dim lastrow As范围
 Dim logrange As Range 
 Dim vlastrow As Range 
 Dim vlastcol As Range 
 Dim copydata As Range 
 Dim pastecell As Range 
 Dim callno As Range 
 
设置wb = GetObject（Book1）
设置ws = wb.Worksheets（Sheet1）
 
 Application.ScreenUpdating = False 
 
'如果我们获取工作簿实例然后
如果不是wb是没有
与wb.Worksheets（Sheet1）
 DisplayAlerts = False 
 ScreenUpdating = False 
 .Cells.RowH 8 = 15 
设置rnglog = wb.Worksheets（Sheet1）。Range（1：1）。Find（What：=Log Date，LookAt：= xlPart，MatchCase：= False，SearchOrder： = xlByColumns，SearchDirection：= xlNext）
设置lastrow = rnglog.EntireColumn.Find（什么：=*，LookAt：= xlPart，MatchCase：= False，SearchOrder = = xlByRows，SearchDirection：= xlPrevious）
设置logrange = wb.Worksheets（Sheet1）。范围（rnglog，lastrow）
 rnglog.EntireColumn.Offset（0，1）.Insert 
 rnglog.EntireColumn.Offset（0，1） .Insert 
 rnglog.EntireColumn.Offset（0，1）.Insert 
 rnglog.EntireColumn.TextToColumns目的地：= rnglog，DataType：= xlDelimited，_ 
 TextQualifier：= xlDoubleQuote，ConsecutiveDelimiter：= True，Tab：= False，_ 
分号：= False，逗号：= False，空格：= True，其他：= False，FieldInfo _ 
：= Array（Array（1，1） （2，1），Array（3,9）），TrailingMinusNumbers：= True 
 rnglog.Value =日志日期
 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
设置vlastrow = wb.Worksheets（Sheet1）。范围（A：A）Find（What：=*，LookAt：= xlPart，MatchCase：= False， SearchOrder：= xlByRows，SearchDirection：= xlPrevious）
设置vlastcol = vlastrow.EntireRow.Find（什么：=*，LookAt：= xlPart，MatchCase：= False，SearchOrder：= xlByColumns，SearchDirection：= xlPrevious） 
设置copydata = .Range（A2，vlastcol）
 copydata.Copy 
结束
使用ActiveWorkbook.Worksheets（RAW数据）
设置pastecell = .Range（A：A）。Find（What：=*，LookAt：= xlPart，MatchCase：= False，SearchOrder：= xlByRows，SearchDirection：= xlPrevious）
设置callno = 1：1）Find（What：=Call No，LookAt：= xlPart，MatchCase：= False，SearchOrder：= xlByColumns，SearchDirection：= xlNext）
 pastecell.Offset（1，0） PasteSpecial xlPasteValues 
 .Cells.RemoveDuplicates列：= 5，标题：= xlYes 
 Application.CutCopyMode = False 
结束
 wb.Close False 
 Application.ScreenUpdating = True 
 MsgBoxDone
 End If 
 End Sub 
  

我以为我会通过关闭RAW数据工作簿来解决这个问题（我也想这样做），但是我得到一个提示，因为剪贴板数据是相当大的，所以这也不行。

解决方案

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

  wb.Application.CutCopyMode = False 
  / pre> 
 
 
代替
  Application.CutCopyMode = False 
  
其中 wb.Application 返回应用程序实例哪个工作簿 wb 属于。
 
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. 

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.

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. 
解决方案
Since workbook wb belongs to another application instance, you should use
wb.Application.CutCopyMode = False
instead
Application.CutCopyMode = False
where wb.Application returns applications instance which workbook wb belongs to.

                        
这篇关于Excel VBA  - 如何在另一个应用程序中清除另一个工作簿上的剪贴板？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！