When Copy pasting from excel to notepad gives double quotes

Question

I'm trying to create a SQL query in a cell from the values in excel. But from when I copy the cell and paste in any window it gives me double quotes. Is there any way to remove this.

Range("H16").Value = "Select distinct(description), merchant_name from(" & vbCrLf & "select distinct description, merchant_name from table_phase" & Range("G3").Value
        Range("H16").Value = Range("H16").Value & vbCrLf & patternString & vbCrLf & "Union all" & vbCrLf
        Range("H16").Value = Range("H16").Value & "select distinct description, merchant_name from table_phase" & Range("G4").Value
        Range("H16").Value = Range("H16").Value & vbCrLf & patternString & vbCrLf & ") order by merchant_name, description"
        Range("H16").Value = Trim(Range("H16").Value)
        Range("H16").WrapText = True
        Range("H16").Select
        Selection.Copy

The range G4 and G3 contins numbers like 24 or 25. The pattern string contains : where merchant_name in ( 'Value1','Value2' )

I have generated the pattern string like this

patternString = "where merchant_name in ("
        For i = 1 To UBound(addedMerch, 1) - 1
            patternString = patternString & " '" & addedMerch(i, 1) & "',"

            If (i - (4 * (i \ 4))) = 0 Then
                patternString = patternString & vbCrLf
            End If
        Next

I have used the next line character to if the patterns are too many. I want to get the SQL query at different lines to avoid confusion

Possible duplicate of [Leave out quotes when copying from cell](http://stackoverflow.com/questions/24910288/leave-out-quotes-when-copying-from-cell) — Vasily, Oct 27 '16 at 08:42

score 1 · Answer 1 · edited May 23 '17 at 12:06

The problem is caused by the line-breaks in the generated text. You can see the same effect if you type a Alt-Enter b into a cell and then copy the cell to NotePad.

The simplest way to get around the issue is not to include vbCrLf sequences when generating cell H16.

Another way would be to generate H16 as you do currently, then copy the value created to another cell (I'll use J16 as an example) replacing all the vbCrLFs with spaces as you do so, and then copy the value of J16 to the clipboard.

Range("H16").Value = "Select distinct(description), merchant_name from(" & vbCrLf & "select distinct description, merchant_name from table_phase" & Range("G3").Value
Range("H16").Value = Range("H16").Value & vbCrLf & patternString & vbCrLf & "Union all" & vbCrLf
Range("H16").Value = Range("H16").Value & "select distinct description, merchant_name from table_phase" & Range("G4").Value
Range("H16").Value = Range("H16").Value & vbCrLf & patternString & vbCrLf & ") order by merchant_name, description"
Range("H16").Value = Trim(Range("H16").Value)
Range("H16").WrapText = True

Range("J16").Value = Replace(Range("H16").Value, vbCrLf, " ")
Range("J16").Copy

SQL treats CR/LFs as whitespace, so it won't care whether they are replaced with a space.

Note: This answer to another question shows how to copy text directly to the clipboard without going via an intermediate cell.

I wanted the query to be in different lines. When I'm pasting the the query to my SQL editor its goes out of screen and its very difficult to understand. I have found a work around which i have posted here. Thanks for your help for understanding the cause of the issue. — Manu Mohan, Oct 27 '16 at 10:50

score 0 · Accepted Answer · answered Oct 27 '16 at 10:47

I wanted to break lines that is why i used vbCrLf, which caused the issue. I have figured a work around. Instead of vbCrLf i have used a pipe delimiter ("|") and pasted the value on a cell. Then using text to column function i have separated the query.

qryString = "Select distinct(description), merchant_name from(" & "|" & "select distinct description, merchant_name from table" & Range("G3").value
        qryString = qryString & "|" & patternString & "|" & "Union all" & "|"
        qryString = qryString & "select distinct description, merchant_name from table" & Range("G4").value
        qryString = qryString & "|" & patternString & "|" & ") order by merchant_name, description"
        qryString = Trim(qryString)
        Range("H14").value = qryString
        Range("H14").WrapText = False
        Range("H14").Select
        Selection.TextToColumns Destination:=Range("I14"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1)), TrailingMinusNumbers:=True
        Range("I14").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Range("H16").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True

When Copy pasting from excel to notepad gives double quotes

2 Answers2