[Excel->Word] Filling in Word document adds unwanted special characters

[Mahar92]

Hello,

I am building an invoice generator that fills in a word template based on values in an Excel table. It all works fine - except for the address, which contains these rectangle-shaped special characters in all address lines
but the first one:

The corresponding cell value in the Excel table is:

The address in the Word template is located in a table cell. Reading out the contents of the cell

Debug.Print WordDoc.Tables(1).Cell(1, 1).Range

returns the following result in the Immediate window:

STEFES BAU GmbH
Pfalzburger Str. 85
28207 Bremen
Deutschland

The VBA code is as follows:

                With WordDoc.Content.Find
                    .Text = "[" & ContainerHeader(LBound(ContainerHeader), h) & "]"
                    .Replacement.Text = Format(Replace(ContainerValues(i, h), Chr(10), vbNewLine), varTypes(1, h))
                    .Wrap = 1 '1 = wdFindContinue
                    .Execute Replace:=2  '2 = wdReplaceAll, '1 = wdReplaceOne
                End With

What can I do to remove these special characters? Does anyone know if this character has a Char code that I can use to remove it?

BTW: If I just straight copy the value from to the Excel cell to the Word document, the formatting is just fine:

 

[NdNoviceHlp]

Hi mahar. The unseen characters are part of the table make up. You need to use the Clean function when transferring from Word to Excel

Importing from Word - bullet marks in cell

I'm importing data from a Table in Word into an Excel spreadsheet, (2003), but after each piece of data there is what I can only describe as a small bullet point - I need this removed if anyone can help?

www.mrexcel.com

HTH. Dave

 

[Mahar92]

Hi mahar. The unseen characters are part of the table make up. You need to use the Clean function when transferring from Word to Excel

Importing from Word - bullet marks in cell

I'm importing data from a Table in Word into an Excel spreadsheet, (2003), but after each piece of data there is what I can only describe as a small bullet point - I need this removed if anyone can help?

www.mrexcel.com

HTH. Dave

Thanks for your answer. I am actually transferring from Excel to Word, not the other way around.
Tried the Clean function, which I did not have on my radar, but sadly it removes all of the line breaks and prints out the address in one line.

I did have success, though, by replacing "vbNewLine" with "vbCr":

So I am using

Replace(ContainerValues(i, h), Chr(10), vbCr)

instead of

Replace(ContainerValues(i, h), Chr(10), vbNewLine)

 

[NdNoviceHlp]

My misunderstanding. Happy to hear that you got it sorted out. Thanks for posting your outcome. Have a nice day. Dave