How do I remove quotes from copied text?

Question

I have some code which copies an Excel cell ready for pasting into other systems. However, it's adding quote marks at the beginning and end. Can anyone tell me why it's doing that and what change I need to make to the code, please?

The code is:

Private Sub Button6_Click()
 Application.ScreenUpdating = False
    Dim xSheet As Worksheet
    Set xSheet = ActiveSheet
        If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
            xSheet.Range("B6").Copy
        End If

    Application.ScreenUpdating = True
End Sub

Those quotes are probably not added by Excel copying. Those quotes are probably added by the program you paste it into. Therefore you cannot prevent that with Excel. You can easily test this by pasting into the text editor instead of your program. If there is no quotes in the editor it comes from your program and not from Excel copying. Then a workaround could do the trick by [copying the value to the clipboard directly](https://stackoverflow.com/questions/14219455/excel-vba-code-to-copy-a-specific-string-to-clipboard) instead of using `.Copy` — Pᴇʜ, Jul 27 '23 at 06:06
Thanks for the answer and sorry for the delay in responding. I have been, and still am, locked out of my work laptop. I'll pop straight back on here when I am back in action and update this post as to what has worked or not worked etc. — youngstubbs, Aug 06 '23 at 00:50

score 2 · Answer 1 · answered Jul 27 '23 at 06:11

I have not personally encountered this specific copy / paste behavior before. However, I do have some code snippets that may help achieve the workflow you are looking for.

As a first step, I suggest inspecting the clipboard contents directly to verify if the extra quotation marks are present there initially.

If the quotation marks exist in the clipboard data already, then my provided code should be able to handle stripping them out before the paste.

However, if the clipboard does not contain the extra quotation marks after the copy, that indicates the quotation marks are being introduced later in the process by other system.

Private Sub Button6_Click()
 Application.ScreenUpdating = False
    Dim xSheet As Worksheet
    Set xSheet = ActiveSheet
        If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
            xSheet.Range("B6").Copy
            ' Check the content of clipboard
            Debug.Print GetClipboard()
        End If

    Application.ScreenUpdating = True
End Sub

Here is some sample code to modify the contents of the clipboard programmatically.

Private Sub SetClipboard(ByVal strInt As String)
    Dim objCB As Object
    Set objCB = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    With objCB
        .settext strInt
        .putinclipboard
    End With
End Sub
Private Function GetClipboard() As String
   Set objCB = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    With objCB
        .GetFromClipboard
        GetClipboard = .GetText
    End With
End Function

Sub DEMO()
    Dim vTemp
    ' Simulate your case, put "ABC" (with quotation mark) on clipboard
    Call SetClipboard("""ABC""")
    vTemp = GetClipboard()
    If Len(vTemp) > 0 Then
        SetClipboard (Mid(vTemp, 2, Len(vTemp) - 2))
    End If
    Debug.Print GetClipboard()
End Sub

Thanks for the answer and sorry for the delay in responding. I have been, and still am, locked out of my work laptop. I'll pop straight back on here when I am back in action and update this post as to what has worked or not worked etc. — youngstubbs, Aug 06 '23 at 00:50

How do I remove quotes from copied text?

1 Answers1