Results 1 to 3 of 3

Pasting As Values Only

This is a discussion on Pasting As Values Only within the Excel Questions forums, part of the Question Forums category; Hi, I am hooking into the CTRL + V paste shortcut with the following code and redirecting it to my ...

  1. #1
    New Member
    Join Date
    Feb 2017
    Posts
    8

    Default Pasting As Values Only

    Hi,

    I am hooking into the CTRL + V paste shortcut with the following code and redirecting it to my custom "PasteAsValues" procedure:

    Application.OnKey "^v", "PasteAsValues"

    In Sub PasteAsValues I am using the following code to paste as values:

    Selection.PasteSpecial Paste:=xlPasteValues

    This works perfectly if the user copies cells within Excel and pastes them.

    However, if I copy a value from NOTEPAD, and paste it into Excel, I get the following runtime error:

    Run-time error '1004':


    PasteSpecial method of Range class failed

    How can I adjust my code so that it pastes as values no matter where the source is I'm copying from? (e.g. Excel, Notepad, a web browser, etc.).

    Matthew

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    8,774

    Default Re: Pasting As Values Only

    I am not a real techie, but I believe any data copied from a source external to Excel will be pasted as value only anyhow, so maybe a syntax like
    Code:
    Sheets("Sheet1").Range("A1").Select
    ActiveSheet.Paste
    Last edited by JLGWhiz; Mar 20th, 2017 at 09:40 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    5,736

    Default Re: Pasting As Values Only

    See if this works for you :

    Code:
    Option Explicit
    
    #If VBA7 Then
        Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
        Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
        Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As LongPtr
        Private Declare PtrSafe Function GetClipboardOwner Lib "user32" () As LongPtr
        Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As LongPtr, lpdwProcessId As Long) As Long
        Private Declare PtrSafe Function GetCurrentProcessId Lib "kernel32" () As Long
    #Else
        Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
        Private Declare Function CloseClipboard Lib "user32" () As Long
        Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As Long
        Private Declare Function GetClipboardOwner Lib "user32" () As Long
        Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwProcessId As Long) As Long
        Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long
    #End If
    
    Private Const CF_TEXT = &H1
    
    Sub PasteAsValues()
        Dim lPid As Long
        Dim oDataObj As Object
        
        OpenClipboard 0
        If GetClipboardData(CF_TEXT) = 0 Then MsgBox "The clipboard is empty.": CloseClipboard: Exit Sub
        CloseClipboard
        GetWindowThreadProcessId GetClipboardOwner, lPid
        If GetCurrentProcessId = lPid Then
            If Application.CutCopyMode <> False Then
                ActiveWindow.RangeSelection.PasteSpecial Paste:=xlPasteValues
            End If
        Else
            If GetClipboardOwner <> 0 Then
                Set oDataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
                oDataObj.GetFromClipboard
                ActiveWindow.RangeSelection = oDataObj.GetText
                Set oDataObj = Nothing
            End If
        End If
    End Sub
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com