| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help by smart people!!!
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... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help by smart people!!!
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 ? -- Gary''s Student - gsnu200745 "rfusee" wrote: 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... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help by smart people!!!
On Sep 19, 4:58 pm, rfusee wrote:
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help by smart people!!!
On Wed, 19 Sep 2007 13:58:04 -0700, rfusee
wrote: 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? -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help by smart people!!!
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 wrote: 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... -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help by smart people!!!
Moose,
There are a couple IF conditions that are met prior to the function being called, is that what is causing this? THANKS!!!! "Moose" wrote: On Sep 19, 4:58 pm, rfusee wrote: 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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help by smart people!!!
Dave,
I use the "line1" & vbCrLf & "line2" .....etc to force new lines within the cell. Is there a better way? THANKS!!!!! "Dave Peterson" wrote: 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 wrote: 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... -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help by smart people!!!
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." "Dick Kusleika" wrote: On Wed, 19 Sep 2007 13:58:04 -0700, rfusee wrote: 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? -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help by smart people!!!
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" "Gary''s Student" wrote: 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 ? -- Gary''s Student - gsnu200745 "rfusee" wrote: 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... |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help by smart people!!!
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. rfusee wrote: Dave, I use the "line1" & vbCrLf & "line2" .....etc to force new lines within the cell. Is there a better way? THANKS!!!!! "Dave Peterson" wrote: 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 wrote: 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... -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help by smart people!!!
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... "rfusee" wrote in message ... 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... |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help by smart people!!!
The correct newline characters on the different platform is:
Windows: Cr-Lf Macintosh: Cr Unix / Linux: Lf "Dave Peterson" wrote in message ... 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. rfusee wrote: Dave, I use the "line1" & vbCrLf & "line2" .....etc to force new lines within the cell. Is there a better way? THANKS!!!!! "Dave Peterson" wrote: 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 wrote: 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... -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help by smart people!!!
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. Alain Vaillancourt wrote: The correct newline characters on the different platform is: Windows: Cr-Lf Macintosh: Cr Unix / Linux: Lf "Dave Peterson" wrote in message ... 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. rfusee wrote: Dave, I use the "line1" & vbCrLf & "line2" .....etc to force new lines within the cell. Is there a better way? THANKS!!!!! "Dave Peterson" wrote: 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 wrote: 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... -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how many people are scheduled between... | Excel Worksheet Functions | |||
| Need help of you people | Excel Worksheet Functions | |||
| Need help of you people | Excel Worksheet Functions | |||
| Counting people | Excel Programming | |||
| This is easy for you people! | Excel Programming | |||
Linear Mode
