how to copy excel carriage return to notepad w/out the quotations
 

How do I copy the carriage return [char(10) or char(32) or alt+enter] and
paste it into notepad without having the quotation marks surround it?

how to copy excel carriage return to notepad w/out the quotations
 

=char(32) is the space character. I've never seen notepad put double quotes
around strings that contain spaces.

But the alt-enters (=char(10)) do cause that.

I used the PutInClipboard routine that Chip Pearson has:
http://www.cpearson.com/excel/clipboard.htm

With this sub:
Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Set MyDataObj = New DataObject

MyDataObj.SetText ActiveCell.Text
MyDataObj.PutInClipboard

End Su

And then pasted (manually) into NotePad. No double quotes were inserted. But I
did see a little square where the alt-enter was.

Chip has instructions that you have to follow (including the tools|references
with "Microsoft Forms 2.0 object library") on that sheet.

BB wrote:

How do I copy the carriage return [char(10) or char(32) or alt+enter] and
paste it into notepad without having the quotation marks surround it?

--

Dave Peterson

how to copy excel carriage return to notepad w/out the quotati
 

Thank you so much for responding. This code works for the ActiveCell but how
can I do this with multiple cells? I know it has to do with properties or
something but I just am unfamiliar with methods, properties, etc. Thanks
again.

I tried changing the ActiveCell code to Selection or something but it
doesn't seem to be working.

"Dave Peterson" wrote:

=char(32) is the space character. I've never seen notepad put double quotes
around strings that contain spaces.

But the alt-enters (=char(10)) do cause that.

I used the PutInClipboard routine that Chip Pearson has:
http://www.cpearson.com/excel/clipboard.htm

With this sub:
Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Set MyDataObj = New DataObject

MyDataObj.SetText ActiveCell.Text
MyDataObj.PutInClipboard

End Su

And then pasted (manually) into NotePad. No double quotes were inserted. But I
did see a little square where the alt-enter was.

Chip has instructions that you have to follow (including the tools|references
with "Microsoft Forms 2.0 object library") on that sheet.

BB wrote:

How do I copy the carriage return [char(10) or char(32) or alt+enter] and
paste it into notepad without having the quotation marks surround it?

--

Dave Peterson

how to copy excel carriage return to notepad w/out the quotati
 

Maybe you can concatenate the stuff you need:

Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Dim myCell As Range
Dim myRow As Range
Dim myRng As Range
Dim myRowStr As String
Dim myStr As String

Set MyDataObj = New DataObject

Set myRng = Selection.Areas(1)

myStr = ""
For Each myRow In myRng.Rows
myRowStr = ""
For Each myCell In myRow.Cells
myRowStr = myRowStr & vbTab & myCell.Text
Next myCell
myRowStr = Mid(myRowStr, Len(vbTab) + 1) 'get rid of leading vbtab
myStr = myStr & vbCrLf & myRowStr
Next myRow
myStr = Mid(myStr, Len(vbCrLf) + 1) 'get rid of leading vbcrlf (2 chars!)

MyDataObj.SetText myStr
MyDataObj.PutInClipboard

End Sub


BB wrote:

Thank you so much for responding. This code works for the ActiveCell but how
can I do this with multiple cells? I know it has to do with properties or
something but I just am unfamiliar with methods, properties, etc. Thanks
again.

I tried changing the ActiveCell code to Selection or something but it
doesn't seem to be working.

"Dave Peterson" wrote:

=char(32) is the space character. I've never seen notepad put double quotes
around strings that contain spaces.

But the alt-enters (=char(10)) do cause that.

I used the PutInClipboard routine that Chip Pearson has:
http://www.cpearson.com/excel/clipboard.htm

With this sub:
Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Set MyDataObj = New DataObject

MyDataObj.SetText ActiveCell.Text
MyDataObj.PutInClipboard

End Su

And then pasted (manually) into NotePad. No double quotes were inserted. But I
did see a little square where the alt-enter was.

Chip has instructions that you have to follow (including the tools|references
with "Microsoft Forms 2.0 object library") on that sheet.

BB wrote:

How do I copy the carriage return [char(10) or char(32) or alt+enter] and
paste it into notepad without having the quotation marks surround it?

--

Dave Peterson


--

Dave Peterson

how to copy excel carriage return to notepad w/out the quotati
 

I also thought of concatenate, but I didn't know if the answer would only be
as simple as changing the ActiveCell property to some Selection property for
multiple cells. However, your concatenation code works great and does what I
need it to. THANK YOU, THANK YOU, You have made my day. I will try to
understand what the code is doing later, since it seems a little different
from what I envisioned. Thanks again for your prompt response and all the
help. Thanks to Chip Pearson as well for the coding help.

"Dave Peterson" wrote:

Maybe you can concatenate the stuff you need:

Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Dim myCell As Range
Dim myRow As Range
Dim myRng As Range
Dim myRowStr As String
Dim myStr As String

Set MyDataObj = New DataObject

Set myRng = Selection.Areas(1)

myStr = ""
For Each myRow In myRng.Rows
myRowStr = ""
For Each myCell In myRow.Cells
myRowStr = myRowStr & vbTab & myCell.Text
Next myCell
myRowStr = Mid(myRowStr, Len(vbTab) + 1) 'get rid of leading vbtab
myStr = myStr & vbCrLf & myRowStr
Next myRow
myStr = Mid(myStr, Len(vbCrLf) + 1) 'get rid of leading vbcrlf (2 chars!)

MyDataObj.SetText myStr
MyDataObj.PutInClipboard

End Sub


BB wrote:

Thank you so much for responding. This code works for the ActiveCell but how
can I do this with multiple cells? I know it has to do with properties or
something but I just am unfamiliar with methods, properties, etc. Thanks
again.

I tried changing the ActiveCell code to Selection or something but it
doesn't seem to be working.

"Dave Peterson" wrote:

=char(32) is the space character. I've never seen notepad put double quotes
around strings that contain spaces.

But the alt-enters (=char(10)) do cause that.

I used the PutInClipboard routine that Chip Pearson has:
http://www.cpearson.com/excel/clipboard.htm

With this sub:
Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Set MyDataObj = New DataObject

MyDataObj.SetText ActiveCell.Text
MyDataObj.PutInClipboard

End Su

And then pasted (manually) into NotePad. No double quotes were inserted. But I
did see a little square where the alt-enter was.

Chip has instructions that you have to follow (including the tools|references
with "Microsoft Forms 2.0 object library") on that sheet.

BB wrote:

How do I copy the carriage return [char(10) or char(32) or alt+enter] and
paste it into notepad without having the quotation marks surround it?

--

Dave Peterson


--

Dave Peterson