Cleaning quotes from clipboard data in VBA

Question

I have a macro that will paste into a selected cell multi-lined data from the clipboard. It will insert new rows for each line. Column A and Row1 contains headers and it will fill it out for any inserted rows.

Sheet1
Header0 Header  Header  Header
Header1 Data        
Header2 Data    Data1
                Data2   Data
Header3 Data

Sometimes it adds additional "" quotes, sometimes it doesn't. Is there a way to clean the clipboard data without removing legitimate quote characters?

Sub ClipboardToRows()
' Split multi-lined data into separate rows for the current selection
' Assumption is that Column A contains row headers
Dim currRange As Range, currCell As Range, pasteCell As Range
Dim rowHeader As String
Dim cellContent
Dim cellStr

Dim clipboard As MSForms.DataObject
Dim str1 As String
Set clipboard = New MSForms.DataObject

clipboard.GetFromClipboard
On Error GoTo clipEmpty
str1 = Trim(clipboard.GetText())

Application.CutCopyMode = False
Set currCell = Selection

rowHeader = Cells(currCell.Row, 1).Value
'Skip Column A
If (currCell.Column > 1) Then
    cellContent = Split(str1, Chr(10))
    For i = LBound(cellContent) To (UBound(cellContent))
        cellStr = Trim(cellContent(i))
        If Len(cellStr) > 0 Then
            Set pasteCell = currCell.Offset(i)
            'Set current cell with line 1
            If i = 0 Then
                currCell.Value = cellContent(i)
            Else
                'If next cell down is not empty or the row header is different
                If (Not IsEmpty(pasteCell.Value)) Or (Cells(pasteCell.Row, 1).Value <> rowHeader) Then
                    pasteCell.EntireRow.Insert
                    Cells(pasteCell.Row - 1, 1).Value = rowHeader
                End If
                currCell.Offset(i).Value = cellContent(i)

            End If
        End If
    Next
End If

clipEmpty:
    If Err <> 0 Then MsgBox "There was an issue with pasting. Please try again."

End Sub

Maybe not quite a duplicate, but related: http://stackoverflow.com/q/24910288/4996248 — John Coleman, Feb 08 '17 at 00:50
Thanks I was looking at that, however in my case my source that I'm copying could be from anywhere not just an Excel cell. So I would just want to get the contents of the clipboard (assumes that user has already hit CTRL+C on something). — Esuriency, Feb 08 '17 at 00:57

score 0 · Answer 1 · answered Feb 08 '17 at 02:03

Not really.

If double quotes are being inconsistently added to the clipboard in the copying process AND there are legitimate quotes in the source data that you want to keep, there's no way from just looking at what's currently in the clipboard to know for certain what was in the original data and what was added in the copying process.

The best you can do is try to recognize any patterns of quotes being added incorrectly and try to remove those.

For example, test each value to determine whether there is a double quote at the beginning and/or at the end, in which case remove the first and/or last character from that value.

Cleaning quotes from clipboard data in VBA

1 Answers1