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

[mcescher]

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.