Access VBA - Find and Replace text for Line Breaks returns weird blocks in my Word document

0
votes

In my Access form a user can enter some text in a text block. Usually there are multiple line breaks in this text (so in the properties of the textbox the enter functionality is changed to return a line break instead of going to another record).

The text that a user types in the form will replace some text in a word document using the VBA find and replace function:

With objWord.Selection.Find
        .Forward = True
        .Wrap = 1
        .ClearFormatting

        .Execute2007 FindText:="{IndexText}", ReplaceWith:=NewIndexText, Replace:=2 'string is less than 255 so replace it

    End With

This works fine, though when the user uses line breaks in their text I get these weird blocks in my Word document:

enter image description here

How can I get to show the Line breaks correctly without the blocks (and also without the Line break symbol).

P.S. I also build in code for handling text with more than 255 characters but just didn't post the code since its unnecessary for this question.

2
vbams-accessreplacems-word
Thank you @CindyMeister Found out that my string contained a LineFeed in Access (Chr10) that Word doesnt understand. I did a replace in my string to set all Chr(10) to an empty string: NewIndexText = Replace(NewIndexText, Chr(10), "") – Nicolas
Great :-) I put the information into an "Answer" :-) – Cindy Meister

2 Answers

1
votes

Word uses only ANSI 13 for paragraph breaks.

(What you call "new line", but a line is something different in Word and is ANSI 11. A Paragraph is generated by pressing Enter; new line by Shift+Enter.)

If a program hands text over to Word that uses ANSI 10 or some combination of 10 and 13, or even something else entirely, you get boxes or other odd behavior. Figure out what the character(s) is(are) and use the Replace function on NewIndexText to replace with ANSI 13.

For example (taken from OP's comment), since Access adds an ANSI 10: 

NewIndexText = Replace(NewIndexText, Chr(10), "")
0
votes

The picture of Word shows that there are some non printable charcaters in the string NewIndexText.

Try to find out which on these are and replace them prior to your routine. To identify them you could use this code:

Dim c as String
For Each c in NewIndexText
  Debug.Print c & " " & Chr(c)
Next