1

I tried making a super basic note generator, which lays out a note for the user to copy and paste into our admin system, but it is adding a trailing and leading double quotes. How do I get rid of them?

I’ve seen the large post here about this and none of the fixes work for me, mainly due to the constraints of the government work place I’m in.

The line breaks are important and must stay. This has to be super simple for the user, I can’t add references or libraries, there can be no extra steps for the user like pasting into another program first ect. There will be no other double quotes in the text.

Can you me help with the excel formula or the Macro?

Thanks

The cell named Notes that I am copying:

=CONCATENATE(C2,CHAR(10),"Error Code: ",A2,": ",B2,CHAR(10),D2,CHAR(10),"T TF Number ",Notes!$C$4," issued",CHAR(10),"T ID: ",Notes!$C$3)

The Macro to Copy:

Sub Copy_Note()
    Range("Note").Select
    Selection.Copy
End Sub

How it pastes:

"Special testing form
Error Code: 12345: This is a test error code
Fixed it real good
1st ref number: 2222222 issued
2nd Ref Number: 111111"

How I want it to look:

Special testing form
Error Code: 12345: This is a test error code
Fixed it real good
1st ref number: 2222222 issued
2nd Ref Number: 111111

L42
  • 19,427
  • 11
  • 44
  • 68
Mathew B
  • 13
  • 1
  • 4
  • Couldn't you just make your `"Note"` range be 5 rows of cells (one for each line of text)? (And I'm not clear from your post why the method of copying the cell to Word, and then copying from Word to the clipboard so that it could be pasted to the admin system, didn't work.) – YowE3K Jul 27 '17 at 03:09
  • The actual note with vary depending on the piece of work the user is doing, sometimes the note might have 2 or 3 variables, more complex ones might have many more. copy paste into word would work, but it is that type of mucking around that I'm trying to avoid. The users already have a bunch or home made word docs open with various note templates. I'm trying to collate and automate all that into one extremely simple to use doc. – Mathew B Jul 27 '17 at 03:16
  • According to all the google hits, excel adds them and some simple text editors show them, like where I'm trying to paste to. – Mathew B Jul 27 '17 at 03:20
  • @ThomasInzina Excel wraps text in double-quotes if it contains special characters such as newline characters – YowE3K Jul 27 '17 at 03:21
  • @YowE3K Thanks for the lesson! –  Jul 27 '17 at 03:35

2 Answers2

2

I just tried the method of copying the cell to Word, and then copying that to the clipboard, and it worked successfully:

Sub Copy_Note()
    Dim wrdApp As Object
    Dim wrdDoc As Object
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = wrdApp.Documents.Add
    Range("Note").Copy
    wrdApp.Selection.PasteExcelTable False, False, False
    wrdApp.Selection.WholeStory
    wrdApp.Selection.Copy
    wrdDoc.Close False
    wrdApp.Quit
End Sub
YowE3K
  • 23,852
  • 7
  • 26
  • 40
  • Oh I see. when I saw the mentions of using Word, I thought the user would literally have to open a new word doc, paste, highlight and copy again. I'm new to VBA and didn't realise it could do that as per your code. That worked brilliantly. Thanks a million! – Mathew B Jul 27 '17 at 03:29
  • @MathewB There are very few things that the user can do that can't be automated. (Note, I wasn't sure how to actually paste into Word and then copy again, so I used its macro recorder to get the necessary Word VBA code - someone will probably tell me that there is a much easier way.) – YowE3K Jul 27 '17 at 03:32
  • @MathewB Thomas Inzina has come up with a better (simpler) method. Please check it out and, if it works for you (it worked for me, so hopefully will work for you too), transfer the "accepted" tick to his answer. – YowE3K Jul 27 '17 at 04:31
1

You may be able to use the clipboards with late binding. This will only work if the range is a single cell. 

With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    .SetText Range("Note").Value
    .PutInClipboard
End With
  • Much easier than using Word as an intermediate destination! – YowE3K Jul 27 '17 at 04:00
  • Thanks, but that did not preserve the line breaks. **I tried:** {Sub COPY_NOTE_2() With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText Range("Note").Value .PutInClipboard End With End Sub} – Mathew B Jul 27 '17 at 04:39
  • @MathewB Strange - I used it and pasted into Notepad++ (which was exhibiting the same behaviour as you said your admin system was) and it successfully pasted without quotes, and it preserved line breaks. (Doesn't preserve line breaks in Notepad, but it wasn't preserving them with a straight copy anyway.) – YowE3K Jul 27 '17 at 04:50