On Dec 30, 11:07*am, Dave Peterson wrote:
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 wrote:

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- 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.