Excel and Notepad: how avoid additional inverted commas after copy and paste?

[marco2]

Hi,
If I write a multi-line text in a cell (then go down with alt+enter)
and after copy the cell pasting on Notepad, it display before inverted
commas and after textual content.
Instead, if I select directly the content from the formula bar, it
isn't happen.
Do exist a way for copy and paste directly from the cell without
select from the formula bar?
Maybe with a macro?

Thank
Marco

__________________
http://www.idee-regalo.biz/catalogo-stereogrammi-3.html http://www.ghisirds.it/
http://scuo.la/ http://www.righettofabrizio.com/pantografo-taglio-plasma.html

 

[Dave Peterson]

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

With this sub:
Option Explicit
Sub testme()
PutOnClipboard ActiveCell.Text
End Sub

And then pasted (manually) into NotePad. No double quotes were inserted.

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

 

[marco2]

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

With this sub:
Option Explicit
Sub testme()
PutOnClipboard ActiveCell.Text
End Sub

And then pasted (manually) into NotePad. No double quotes were inserted.

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

In the meantime, thank you for the reply but Notepad was an example.
Really I must paste the cell content on other programs that operate as
Notepad.
Then the contents without inverted commas have to stay only on
clipboard.
Is it possible?
Thank you very much!
Marco

 

[Dave Peterson]

I don't know. I think it'll depend on how the other program(s) behave.

What happened when you tried it using Chip's code and your program(s).

 

[marco2]

I don't know. I think it'll depend on how the other program(s) behave.

What happened when you tried it using Chip's code and your program(s).

thanks to all!
I solved the problem and it is work.


Sub TestMacro()
Dim objClip As DataObject
' Microsoft Forms 2.0 Object Library
' needs to be selected as a Reference (on the VBA Tools menu)
Dim strTmp As String
Set objClip = New DataObject
strTmp = Replace(ActiveCell.Value, Chr(10), vbCrLf)
objClip.SetText strTmp
objClip.PutInClipboard
Set objClip = Nothing
End Sub


best regards.

 

[Guest]

this sounds like exactly what i need to do but i am not familiar with VBA.
can you let me know how i can do this in excel for the cells i need to past
without the quotation marks?

 

[Dave Peterson]

You can start by reading David McRitchie's notes about macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And don't forget to read Chip Pearson's specific instructions on that previous
link.

 

[Guest]

if i have a formula like =W21&CHAR(13)&(CHAR(10))&Y21
where W21 and Y21 are text...do you know how i can copy the results of this
cell and paste it into notepad without getting the quotes inserted?

 

[Dave Peterson]

Did you try the macro?

Since you have the CR and LF in the cell, you could modify it like:

Option Explicit
Sub TestMacro()
Dim objClip As DataObject
' Microsoft Forms 2.0 Object Library
' needs to be selected as a Reference (on the VBA Tools menu)
Dim strTmp As String
Set objClip = New DataObject
strTmp = ActiveCell.Value
objClip.SetText strTmp
objClip.PutInClipboard
Set objClip = Nothing
End Sub

Assumes that the activecell is the cell with that formula.

 

[Guest]

I ran this macro on the cell but i still am unable to copy and paste without
the quotes.

 

[Dave Peterson]

The macro does the copy.

Run the macro (which does the copy)
then paste into Notepad

I ran this macro on the cell but i still am unable to copy and paste without
the quotes.

 

[Guest]

oh yes that does work..thank you.. but how do i get it to run that macro in
that column when i open the worksheet?

 

[Guest]

actually i would like it to do multiple cells in a column each having that
formula and be able to paste it into notepad. is that possible?

 

[Dave Peterson]

Maybe you can start with this code and change it to what you need.

It grabs all the cells in C1:C### (where ### is the last row used in column C).

Option Explicit
Sub TestMacro()
'Microsoft Forms 2.0 Object Library
'needs to be selected as a Reference (on the VBA Tools menu)

Dim objClip As DataObject
Dim myCell As Range
Dim myRng As Range
Dim myStr As String

With Worksheets("sheet1")
Set myRng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
End With

myStr = ""
For Each myCell In myRng.Cells
myStr = myStr & vbCrLf & myCell.Value
Next myCell

myStr = Mid(myStr, Len(vbCrLf) + 1)

Set objClip = New DataObject
objClip.SetText myStr
objClip.PutInClipboard
Set objClip = Nothing

End Sub

You could run it by hitting alt-f8 and running the code from that dialog.

actually i would like it to do multiple cells in a column each having that
formula and be able to paste it into notepad. is that possible?

 

[Guest]

I am getting stuck on the following:
With Worksheets("sheet1")

any ideas?

 

[Gord Dibben]

"Stuck" means what?

Error message and macro stops?

Do you have a "sheet1" in your workbook?

Worksheet names are case-sensitive.


Gord Dibben MS Excel MVP

I am getting stuck on the following:
With Worksheets("sheet1")

any ideas?

 

[Dave Peterson]

When used like this:
With worksheets("Sheet1")
the name isn't case sensitive.

But I bet Gord hit the nail on the head when he asked if you have a sheet named
Sheet1 in your workbook.

 

[Guest]

Run-time error '9': Subscript out of range.

"Stuck" means what?

Error message and macro stops?

Do you have a "sheet1" in your workbook?

Worksheet names are case-sensitive.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Change Sheet1 to the name of the sheet with the data.

Run-time error '9': Subscript out of range.

 

[Gord Dibben]

Well, I'll be jiggered.

Always thought it was.

Just never checked it out.


Gord