每当我尝试粘贴Excel VBA粘贴特殊方法都会失败 [英] Excel vba paste special method fails whenever i try to paste

问题描述

大家好，我在vba刚刚起步，我试图捕获工作表上的所有ctrl + v事件并将剪贴板上的所有内容粘贴到当前选定的单元格中。大多数时候，我要复制的内容是来自firefox或记事本EI客户端名称或我们网站上的电话号码的文本，但是该代码仅在我尝试粘贴到de cell本身（在单元格编辑模式下）时有效。

Hi everyone I'm fairly new at vba I'm trying to capture all ctrl+v events on my sheet and paste whatever is on the clipboard to the currently selected cell. Most of the time what i want to copy is text from firefox or from notepad E.I client's name or phonenumber that is n our website however the code only works when i try to paste inside de cell itself (in cell edit mode)

    Private Sub CopyPaste()

'PasteSpecial Values Only
  ActiveCell.PasteSpecial Paste:=xlPasteValues, skipblanks:=True

这会返回运行时错误1004范围类失败的特殊方法
i也有尝试过此操作，但它也返回错误

this returns a runtime error 1004 pastespecial method of range class failed i have also tried this but it returns a error too

activecell.PasteSpecial Format:="Text", skipblanks:=True, link:=False, DisplayAsIcon:=False

请注意，仅粘贴文本和仅粘贴值的主要原因是因为我的excel的单元格布局非常具体，带有颜色和其他内容，普通的粘贴会使所有内容弄乱。

As a note the main reason for pasting text only and values only is because my excel has a very specific cell layout with colors and other stuff and a normal paste messes everything up.

如果有人可以帮助我，我会很喜欢

I would love it if anyone could help me out

推荐答案

Ra nge.PasteSpecial 方法将复制的 Range 粘贴到指定的 Range 中。因此，这仅适用于范围：

Range.PasteSpecial method pastes a Range that has been copied into the specified Range. So this will work for Ranges only:

ActiveCell.PasteSpecial Paste：= xlPasteValues，skipblanks：= True

需要直接访问剪贴板。
如何使用剪贴板此处。

Clipboard needs to be accessed directly. How to use clipboard e.g. here.

Option Explicit

Sub Init()
    Application.OnKey "^{v}", "CopyPaste"
End Sub

Public Sub CopyPaste()       
    ' MSForms.DataObject can be used when MSForms lib. is referenced
    Dim clipboard As Variant 

    Dim strContents As String

    ' http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
    ' When no MSForms is referenced yet.
    Set clipboard = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") 
    clipboard.GetFromClipboard
    strContents = clipboard.GetText
    ' Parse or format strContent if needed
    ActiveCell.Value = strContents
End Sub

这篇关于每当我尝试粘贴Excel VBA粘贴特殊方法都会失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！