Mail merge date format

[Trueblue862]

Hi, I have this code which I adapted from one I found on this site. One of the issues I'm having with it is it is messing with my date formats. I need my dates to be in"DD/MM/YYYY" and it is giving me "MM/DD/YYYY". Any help with this would be appreciated.

Sub DoMailMerge()
'Note: A VBA Reference to the Word Object Model is required, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName
With wdApp
  'Disable alerts to prevent an SQL prompt
  .DisplayAlerts = wdAlertsNone
  'Open the mailmerge main document
  Set wdDoc = .Documents.Open("H:\Reception\New reception (working).docx", _
    ConfirmConversions:=False, ReadOnly:=True, AddToRecentfiles:=False)
  With wdDoc
    With .MailMerge
      'Define the mailmerge type
      .MainDocumentType = wdFormLetters
      'Define the output
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      'Connect to the data source
      .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
        LinkToSource:=False, AddToRecentfiles:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;Data Source=strWorkbookName;" & _
        "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
        SQLStatement:="SELECT * FROM `Mail Merge$`", _
        SubType:=wdMergeSubTypeAccess
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      'Excecute the merge
      .Execute
      'Disconnect from the data source
      .MainDocumentType = wdNotAMergeDocument
    End With
    'Close the mailmerge main document
    .Close False
  End With
  'Restore the Word alerts
  .DisplayAlerts = wdAlertsAll
  'Display Word and the document
  .Visible = True
End With
End Sub

 

[Trevor G]

You would have to use a switch in your date field within the document. In the document select the date field and use right mouse button and select toggle field change the field as indicated below, then toggle the field again. You then need to move to the next record to see the change you've made then save and it should format the date field in the way you want.

Format a date merge field, by using the \@ switches. For example, to display a date field as "12/30/2020", the merge field should be defined as { MERGEFIELD myField \@ dd/MM/yyyy}
yyyy = Years
MM = Months must be uppercase otherwise Word thinks its minutes
dd = Day

 

[Trueblue862]

Ok, thank you, I’ll give it a try.

 

[Trueblue862]

I finally had time to try this, it worked perfectly. Thank you.

 

[Trevor G]

Happy to help and thank you for letting me know you have a solution.

 

[Macropod]

See Mailmerge Date Formatting in the Mailmerge Tips and Tricks thread at:

Mailmerge Tips & Tricks