Preserve formatting during mail merge using VBA

Hi All

I have created a Word document and Excel workbook to do a mail merge. The word document contains bookmarks to indicate where merged fields should be added. All the work from creating the connection to adding the merge fields happens in a macro in the Excel workbook. Everything works fine. The only problem that i'm having is that formatting is not carried over into the Word document form the Excel workbook.  For example in the Excel wb a field might be a currency field with 2 decimal places when it is taken to the word document it does not retain this formatting and instead of $12.30 i end up with 12.3012943. I do not mind so much about the "$" signs as i can easily add this to the Word document the decimal places is the real issue. I have seen that when you add a field manually in a Word document and right click and Edit it there is a check box to Preserve formatting but i cannot find how to this with VBA. I have also tried to record a macro doing this but Word does not allow me to right click while recording. I have added the code i use below.
Sub DoMerge()
Dim appWd As Word.Application
Dim WdDoc As Word.Document
Dim strBookFullName As String
Dim cell As Range
Dim wdFind As Object
strBookFullName = ActiveWorkbook.FullName

Set appWd = CreateObject("Word.Application")
appWd.Visible = True

With appWd
    Set WdDoc = appWd.Documents.Open("C:\Users\Elmo\Documents\Aaron\2nd Project 2017\WorkingOn\VendorRebateClientDoc4March17_2.docx")
    WdDoc.Activate
    WdDoc.MailMerge.OpenDataSource Name:=(strBookFullName), _
    ReadOnly:=True, LinkToSource:=0, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:="", SQLStatement:="SELECT * FROM `MergeRange`", SQLStatement1:=""

Set wdFind = appWd.Selection.Find
    'finds text in word doc and replace with merge field
    For Each cell In Range("MergeText")
        
        wdFind.Text = cell.Value
        
        Dim MyDoc As String, txt As String, t As String
        MyDoc = WdDoc.Range.Text
            txt = cell.Offset(0, 1).Value
            t = Replace(MyDoc, txt, "")
            X = (Len(MyDoc) - Len(t)) / Len(txt)
            'ActiveDocument.Bookmarks("Cname").Select
            ActiveDocument.MailMerge.Fields.Add Range:=WdDoc.Bookmarks(cell.Offset(0, 2).Value).Range, Name:=txt
    Next cell
    
End With

End Sub

Open in new window


Thanks in advance
Elmo
LVL 1
cErasmusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cErasmusAuthor Commented:
Hi I have now tested using the Preserve formatting checkbox and it also does not do what i need.
Rgonzo1971Commented:
Hi,

in the field you could use the format switch
 { MERGEFIELD Field1 \# $0.00 }

Open in new window

Regards
cErasmusAuthor Commented:
Hi Rgonzo1971
How would i implement it in my code?
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
cErasmusAuthor Commented:
Hi
I have now gone and in the Excel wb rounded all the values to 2 decimal points in another range i then copied and pasted the values to the range that i use for the mail merge and i still end up with odd numbers as per the attached image. Any ideas on why this happens?
 Capture.PNG
GrahamSkanRetiredCommented:
The 'Preserve formatting' checkbox ensures that the \* MERGEFORMAT switch is added to the field code. This tells the merge to copy the Font format from the merge field itself.

The modern default data transfer method is OLE DB. This works on the data alone. It ignores the display format that is set in the data source application. The original transfer method was DDE, which does take account of that display format . The method can be changed in the dialogue that opens when the merge is started if the Word option 'Confirm file format conversion on open' is set. It is not to be recommended. Rather use rgonzo's solution, unless you have many fields to change.
Rgonzo1971Commented:
the fields in word have to be changed
GrahamSkanRetiredCommented:
It would be possible to add the switch in VBA, but it is far easier to do it in the Merge Main document.

The VBA code would need to be told somehow which merge fields need to have the switch added.
cErasmusAuthor Commented:
Graham could you perhaps give me an example of how the switch will be added in VBA?

The purpose of this is that a client will use the Excel wb and Word doc as a template to do create documents so this means that everything needs to happen with the click of a button.

Elmo
GrahamSkanRetiredCommented:
Note, to see and edit the actual code for all the fields in a document, use the Alt+F9 shortcut. It is a toggle so can be used to hide the codes again when editing is finished.
GrahamSkanRetiredCommented:
Cross posted. Will do.
ShumsExcel & VBA ExpertCommented:
Format cash amounts to 2 decimal places

Sometimes when merging data, comprising cash amounts, especially when importing from Excel, the mergefield displays up to 14 decimal places. This is caused by the way Excel handles numeric data internally, but is easily tamed by the addition of a switch to limit the data to 2 decimal places. e.g.
Serial7Serial8
are produced by the following 2 fields respectively
Serial9 MergeAmount  
In the second field, the switch \# "$,0.00;($,0.00)" includes a comma, which provides for the commas to indicate thousands and millions e.g.

and an optional currency symbol - here a dollar sign.The numeric mask can also be expressed as "$,#.00;($,#.00)" where the '#' will suppress the 0 for amounts less than a dollar.
ShumsExcel & VBA ExpertCommented:
When setting up the mail merge, use Alt + F9 to reveal the Mail Merge codes. The address field will look something like this.
{ MERGEFIELD Address }

Open in new window

GrahamSkanRetiredCommented:
This code assumes that the relevant field names are distinguished with the prefix "cash".
Sub AddFieldFormatSwitches(doc As Word.Document)
    Dim fld As Word.Field
    Dim strFieldText As String
    Dim strFieldWords() As String
    Dim strFieldName As String
    
    For Each fld In doc.Range.Fields
        If fld.Type = wdFieldMergeField Then
            strFieldText = Trim(fld.Code.Text)
            strFieldWords() = Split(strFieldText, " ")
            strFieldName = strFieldWords(1) '2nd word
            If LCase(Left(strFieldName, 4)) = "cash" Then
                fld.Code.Text = strFieldText & " \# $0.00"
            End If
        End If
    Next fld
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cErasmusAuthor Commented:
Thanks all i have it working now. I will close the question in a bit and add how i did it
cErasmusAuthor Commented:
Sorry one last question. In the Excel wb i have 3% and 4.2% what would the switch look to get this? I'm getting 0.03% and 0.04% this is what i used \#"0.00%"
cErasmusAuthor Commented:
Ok it seems the easy way around this is just to multiply by 100.
ShumsExcel & VBA ExpertCommented:
In Excel if cell is formatted as percentage, then multiply by 1.
Suppose you are getting 0.03 % where cell is formatted as percentage then =0.03*1, it should display 3%
cErasmusAuthor Commented:
Below is the code that i used.
Sub DoMerge()
Dim appWd As Word.Application
Dim WdDoc As Word.Document
Dim strBookFullName As String
Dim cell As Range
Dim wdFind As Object
strBookFullName = ActiveWorkbook.FullName

Set appWd = CreateObject("Word.Application")
appWd.Visible = True

With appWd
    Set WdDoc = appWd.Documents.Open("C:\Users\Elmo\Documents\Aaron\2nd Project 2017\WorkingOn\VendorRebateClientDoc4March17_2.docx")
    WdDoc.Activate
    WdDoc.MailMerge.OpenDataSource Name:=(strBookFullName), _
    ReadOnly:=True, LinkToSource:=0, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:="", SQLStatement:="SELECT * FROM `MergeRange`", SQLStatement1:=""

Set wdFind = appWd.Selection.Find
    'finds text in word doc and replace with merge field
    For Each cell In Range("MergeText")
        
        wdFind.Text = cell.Value
        
        Dim MyDoc As String, txt As String, t As String
        MyDoc = WdDoc.Range.Text
            txt = cell.Offset(0, 1).Value
            ActiveDocument.MailMerge.Fields.Add Range:=WdDoc.Bookmarks(cell.Offset(0, 2).Value).Range, Name:=txt & cell.Offset(0, 3).Value
    Next cell
    
End With

End Sub

Open in new window

Rebate.JPGI added the above to a hidden sheet Col F is the range MergeText and does not really do much G is the merged field to be inserted H is the bookmark in the word document and I is the format switch.

Once again thank you very much for the help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.