Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace a space with carriage return in excel | Excel Discussion (Misc queries) | |||
Can I put a carriage return in an EXCEL cell | Excel Discussion (Misc queries) | |||
Excel import txt file carriage return as delimiters | Excel Discussion (Misc queries) | |||
How do I add a carriage return into a cell in Excel? | Excel Discussion (Misc queries) | |||
Carriage Return in Excel | Excel Discussion (Misc queries) |