Need help by smart people!!!

[rfusee]

I posted this yesterday, but noone replied. I'm hoping it's not because
there's no solution to my problem! Here's the deal...

I have a Cell in Excel which calls a VBA function which returns a long
string. The VBA function works correctly and the output looks correct in the
Cell. When I copy and paste that cell from Excel into Notepad, it adds
quotation marks around the beginning and end of the text. I can't figure out
how to make it so that doesn't happen.

Furthermore, if I copy and paste the adjacent Cell in Excel, which is also a
long string but one which I typed directly into the cell and not from a VBA
function call - there are not quotation marks around the text in Notepad!

Very, very frustrating...any help would be most appreciated...

 

[Gary''s Student]

1. Open Notepad
2. Open Excel with a fresh, new, workbook
3. Enter the following UDF:

Function hello() As String
hello = "hello"
End Function

4. In cell A1 of any sheet enter:
=hello()

5. Select the cell; copy the cell; click in Notepad and paste

On my configuration (WinXP and Excel 2002), no quotes (single or double)
appear.

What is you configuration ?

 

[Moose]

I posted this yesterday, but noone replied. I'm hoping it's not because
there's no solution to my problem! Here's the deal...

I have a Cell in Excel which calls a VBA function which returns a long
string. The VBA function works correctly and the output looks correct in the
Cell. When I copy and paste that cell from Excel into Notepad, it adds
quotation marks around the beginning and end of the text. I can't figure out
how to make it so that doesn't happen.

Furthermore, if I copy and paste the adjacent Cell in Excel, which is also a
long string but one which I typed directly into the cell and not from a VBA
function call - there are not quotation marks around the text in Notepad!

Very, very frustrating...any help would be most appreciated...

It sounds like you're coping a formula from the first cell. That's
why you have the quotation marks.

 

[Dick Kusleika]

I have a Cell in Excel which calls a VBA function which returns a long
string. The VBA function works correctly and the output looks correct in the
Cell. When I copy and paste that cell from Excel into Notepad, it adds
quotation marks around the beginning and end of the text. I can't figure out
how to make it so that doesn't happen.

Furthermore, if I copy and paste the adjacent Cell in Excel, which is also a
long string but one which I typed directly into the cell and not from a VBA
function call - there are not quotation marks around the text in Notepad!

Very, very frustrating...any help would be most appreciated...

I can't reproduce with a 100 character string. Can you post the code your
using, or a simplified version that exhibits the same behavior?

 

[Dave Peterson]

I bet you have alt-enters in your cell to force new lines within the cell.

Maybe you can do the copying in your code:

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.

 

[rfusee]

Moose,

There are a couple IF conditions that are met prior to the function being
called, is that what is causing this?

THANKS!!!!

 

[rfusee]

Dave,

I use the "line1" & vbCrLf & "line2" .....etc to force new lines within the
cell. Is there a better way?

THANKS!!!!!

 

[rfusee]

Sure, here is one of the functions that I call which does this
behavior...it's very simple stuff, but I am brand new to this so I could be
doing something stupid...


Function MRBJMS(ServNum As Integer, WorkNum As Integer, Storage As String)
As String

MRBJMS = "text text text text text" _
& "text text text text" _
& "text text text text" & vbCrLf & "1. " _
& ServNum & " Server and " & WorkNum & " license(s) to ....... " _
& "text text text" _
& "center." & vbCrLf & "2. " & Storage & " of combined backup storage
(compressed)." _
& vbCrLf & "3. more text." _
& vbCrLf & "4. more text." _
& vbCrLf & "5. more text." _
& vbCrLf & "6. more text. " _
& "more text." _
& vbCrLf & "7. more text." _
& vbCrLf & "8. more text." _
& vbCrLf & "9. more text."

 

[rfusee]

OK...I did what you said and I do not get any quotes around Hello when I
paste into Notepad either.

So, I went ahead made a small change to the function as follows:

Function Hello () as String

Hello = "Hello" & vbCrLf & "Hello"

End Function

Guess what? I got this output WITH quotes...

"Hello
Hello"

 

[Dave Peterson]

I'd use:

"line1" & vbLf & "line2"
(vblf = alt-enter)
or
"line1" & vbnewline & "line2"
(vbnewline is platform independent--it'll be vblf in windows and vbcrlf on Macs
(I think).)

But both of them won't fix the problem when you paste to NotePad.

Did you try the code taken from Chip Pearson's site?

It worked ok for me.

 

[Alain Vaillancourt]

You must have a new-line character in the string returned by the VBA
function. If you enter a short string in a cell with a new-line character
(Alt-Enter) you will have the same result. It is related to the way the
Paste function work, in Notepad you have no other choice than pasting as
Unformatted Text so the simplest way would be to use WordPad instead, the
default pasting method in NotePad will not put quotation marks around the
string...

 

[Alain Vaillancourt]

The correct newline characters on the different platform is:
Windows: Cr-Lf
Macintosh: Cr
Unix / Linux: Lf

 

[Dave Peterson]

To force a new line within a cell in excel for windows is alt-enter (char(10) or
vblf). (I don't use a Mac.)

If you meant plain old text files, then never mind.