Losing Quotes

[mcescher]

Hi All,
I often have a need to create a list of quote encased, comma separated
values and generally they are in or can be created in Excel. So, I
created a macro to do the hard work for me, so I didn't have to create
formulas every time I wanted to do this.
I wrote the following code and it works fine except the very first
element loses the quotes. Perhaps I highlight:

Jan
Feb
Mar

I want to be able to paste “Jan”, “Feb”, “Mar”
but, it’s coming back as Jan, “Feb”, “Mar”
The debug.print statement works fine, but putting it on the clipboard
messes it up. Any suggestions?

Thanks!

Sub camBuildString()
''Take the highlighted range and make it into a list of comma
separated values
Dim strQt As String, strOut As String, cell As Range
strQt = """": strOut = ""
For Each cell In Application.Selection
If Not IsEmpty(cell.Value) Then
strOut = strOut & strQt & cell.Value & strQt & ", "
End If
Next
Debug.Print (Left(strOut, Len(strOut) - 2))
'PutOnClipboard (Left(strOut, Len(strOut) - 2))
PutOnClipboard strOut
End Sub

 

[Dave Peterson]

This worked for me:

Option Explicit
Sub camBuildString()

Dim DataObj As MSForms.DataObject

'Take the highlighted range and make it into a list of
'comma separated Values

Dim strQt As String
Dim strOut As String
Dim cell As Range

Set DataObj = New MSForms.DataObject

strQt = """" 'or just chr(34)
strOut = ""

For Each cell In Application.Selection
If Not IsEmpty(cell.Value) Then
strOut = strOut & strQt & cell.Value & strQt & ", "
End If
Next cell


DataObj.SetText Left(strOut, Len(strOut) - Len(", "))
DataObj.PutInClipboard

End Sub

Chip Pearson has lots of notes:
http://www.cpearson.com/excel/Clipboard.aspx

ps. If it's reall CSV, then I wouldn't include the additional space character
in the delimiter.

 

[mcescher]

This worked for me:

Option Explicit
Sub camBuildString()

    Dim DataObj As MSForms.DataObject

    'Take the highlighted range and make it into a list of
    'comma separated Values

    Dim strQt As String
    Dim strOut As String
    Dim cell As Range

    Set DataObj = New MSForms.DataObject

    strQt = """" 'or just chr(34)
    strOut = ""

    For Each cell In Application.Selection
        If Not IsEmpty(cell.Value) Then
            strOut = strOut & strQt & cell.Value & strQt & ", "
        End If
    Next cell

    DataObj.SetText Left(strOut, Len(strOut) - Len(", "))
    DataObj.PutInClipboard

End Sub

Chip Pearson has lots of notes:http://www.cpearson.com/excel/Clipboard.aspx

ps.  If it's reall CSV, then I wouldn't include the additional space character
in the delimiter.






--

Dave Peterson- Hide quoted text -

- Show quoted text -

Well, I thank you for your help, and I figured it out. When I pasted
your code in, that gave me the same results when I pasted into Excel.
But when I pasted into Notepad, it worked fine from either piece of
code. Must be Excel removing the quotes when I paste.

Also, thanks for the link, lots of good stuff there.

Thanks again,
Chris M.