-2

I have a fairly simple excel vba script to generate word documents from individual page templates. It's just a glorified search and replace to substitute template keys for cell values.

When I try and copy a multiline cell into word, or insert a vbCr or vbNewLine using the method:

strReplace = TagArray(i, j)
strReplace = Replace(strReplace, Chr(10), vbCr)
strReplace = Replace(strReplace, vbNewLine, vbCr)

It just shows up in word as a ascii box character. If I just let Chr(10) or Chr(10) Chr(13) go through, word discards it and has no line breaks.

How the heck do I preserve the multiline cell?

Couldn't figure it out, so I borrowed the technique from here:

Generate Word Documents (in Excel VBA) from a series of Document Templates

and got rid of the Replace() and changed it too:

'Iterate through all of the keys to be replaced
    For j = 0 To KeyCount - 1
        strReplace = TagArray(i, j)
        clipBoard.SetText IIf(strReplace = vbNullString, "", strReplace)
        clipBoard.PutInClipboard

        For Each storyRange In OutputDoc.StoryRanges
            Do
            With storyRange.Find
                .MatchWildcards = True
                .Text = KeyArray(j)
                .Replacement.Text = "^c"
                '.Replacement.Text = strReplace
                .Execute Replace:=2
            End With
            Set storyRange = storyRange.NextStoryRange
        Loop While Not storyRange Is Nothing
      Next

    Next j

Next i
Community
  • 1
  • 1
Justin
  • 113
  • 2
  • 7
  • Change `vbCr` to `vbCrLf`. A *carriage return* vbCrLf is Chr(13)&Chr(10); a *line feed* vbLf is Chr(10). It's the difference between hitting Enter and hitting Shift+Enter in Word. –  Jan 07 '16 at 00:10
  • Still have a box in the xlsx - this is killing me; when I was using this for plain text a vbNewLine worked fine. – Justin Jan 07 '16 at 01:20
  • I ended up copying a bit from this excellent post: http://stackoverflow.com/questions/19962231/from-a-vba-excel-file-open-a-word-template-enter-a-number-and-print and passing everything through the clipboard – Justin Jan 07 '16 at 01:43

1 Answers1

1

Word uses neither Chr(10) nor Chr(13)&Chr(10). Word uses Chr(13) for new paragraphs and Chr(11) for new lines. Note that these will NOT look correct in Excel, only in Word - there is no "common denominator" for new lines in the two applications!

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43