Mail merge date format

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
150
Office Version
  1. 365
Platform
  1. Windows
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.


VBA Code:
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
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.

Rich (BB code):
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

Board Regular
Joined
May 24, 2020
Messages
150
Office Version
  1. 365
Platform
  1. Windows
I finally had time to try this, it worked perfectly. Thank you.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Happy to help and thank you for letting me know you have a solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,355
Messages
5,601,142
Members
414,431
Latest member
JustmemyselfandI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top