How to stop Excel adding quotation marks when copying to notepad?

  • Thread starter Thread starter AI32768
  • Start date Start date
A

AI32768

I'm just interested in stopping Excel adding quotation marks around text
pasted from copied cells into notepad without having to go into notepad and
do endless 'find and replace's afterwards.

Formatting the cells as text makes no difference so why is Excel adding the
quotes in only around cells containing IP addresses? (And putting a tab after
the first quote marks!)
 
I think it depends on what's in your cells.

You could take a macro approach.

Saved from a previous post:

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 Sub

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.

=========

If you wanted to copy multiple cells, this may help you:

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

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
I'm just interested in stopping Excel adding quotation marks around text
pasted from copied cells into notepad without having to go into notepad and
do endless 'find and replace's afterwards.

Formatting the cells as text makes no difference so why is Excel adding the
quotes in only around cells containing IP addresses? (And putting a tab after
the first quote marks!)
Click to expand...

I was able to get around this issue by highlighting all of my text in Excel, Copying it, then open a new Wordpad document. In Wordpad go to Edit menuand select "Paste Special" then choose "Unformatted Text" and hit OK. Next save your Wordpad document as a text file. No more extra quotes.
 
(e-mail address removed) pretended :
I was able to get around this issue by highlighting all of my text in Excel,
Copying it, then open a new Wordpad document. In Wordpad go to Edit menu and
select "Paste Special" then choose "Unformatted Text" and hit OK. Next save
your Wordpad document as a text file. No more extra quotes.
Click to expand...

Perhaps Excel thinks you're trying to create a tab delimited text file?

It would be much, much faster/easier/better to dump the data into an
array and write the data directly to a text file using standard VB file
I/O functions. No fuss, no muss, just pure data dumped into a plain
text file!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top