Vba excel to word format issue

[Largey980]

Hi,

After some help with excel to word macro, trying to populate letter templates from raw date pasted from another system.

Word templates all

[TABLE="align: left"]
<tbody>[TR]
[TD="align: left"]Title First_Name Surname
Address_1
Address_2
Address_3
Town
County
Postcode
[/TD]
[/TR]
</tbody>[/TABLE]


----
Code im using


Sub FindReplaceInWord()


Dim Wbk As Workbook: Set Wbk = ThisWorkbook
Dim Wrd As New Word.Application
Dim Dict As Object
Dim RefList As Range, RefElem As Range


Wrd.Visible = True
Dim WDoc As Document
Set WDoc = Wrd.Documents.Open("file path to word doc")


Set Dict = CreateObject("Scripting.Dictionary")
Set RefList = Wbk.Sheets("Sheet1").Range("C2:C16")


With Dict
For Each RefElem In RefList
If Not .Exists(RefElem) And Not IsEmpty(RefElem) Then
.Add RefElem.Value, RefElem.Offset(0, 1).Value
End If
Next RefElem
End With


For Each Key In Dict
With WDoc.Content.Find
.Execute FindText:=Key, ReplaceWith:=Dict(Key)
End With
Next Key


End Sub
----
This works fine, until address 2/3 blank and leaves blank line or adds a 0 to word templates.


I tried using =CONCATENATE(Address_1, CHAR(10), Address_2,CHAR(10),Address_3), changed Town to ref address_2, postcode to address_3, left county blank.. which is ok as blank entries at bottom, when copies to word however it appears all on same line.

I need way to remove blank lines, or get the concatenate to copy to sep lines.

I considered using mail merge but I couldn't get it to work with raw data.

Any suggestions?

 

[wideboydixon]

What about this:

    If Dict(Key) = "" Then
        .Execute FindText:=Key & "^p", ReplaceWith:=Dict(Key)
    Else
        .Execute FindText:=Key, ReplaceWith:=Dict(Key)
    End If

WBD

 

[Largey980]

Works perfect! Massive appreciation