question

LeeMorse-6664 avatar image
0 Votes"
LeeMorse-6664 asked LeeMorse-6664 commented

VBA Mail Merge Date - Issue: Swapping Month and Day

0


I have a spreadsheet that I am using to mail-merge with a Word template document. The mail merge is triggered by VBA code. I have some dates which come out correctly in the mail-merge and others that show incorrectly.

The Word-doc merge field is formatted thus: {MERGEFIELD DUE@"dd MMM yyyy hh:mm AM/PM"}

The Excel cell has the following date: "01/07/2021 10:58:00 AM" and has a custom format of "dd mmmm yyyy hh:mm AM/PM" so that it shows as "01 July 2021 10:58 AM"

However, when merged this date comes out as: "07 January 2021 10:58 AM"

We are using the UK date format. The date\time on the PC is set as: Short Date: 24/06/21

The VBA code that triggers the mail merge is:

Set wdDoc = wdApp.Documents.Open(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
With wdDoc
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False,
LinkToSource:=False, Connection:="Provider=Microsoft.ACE.DDE.12.0;User ID=Admin;" &

"Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
SQLStatement:="SELECT * FROM FTR$"
For i = 1 To .DataSource.RecordCount
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource

..... etc

I have aslo tried the following variations in the Word Template:
{MERGEFIELD DUE\@ "dd MMM yyyy hh:mm AM/PM"}
{MERGEFIELD DUE \@ "dd MMM yyyy hh:mm AM/PM"}
{MERGEFIELD DUE \@ dd MMM yyyy hh:mm AM/PM}
Can anybody give me some guidance on how I can make this mail-merge more reliable?

UPDATE: I have now tried several different date formats in both the spreadsheet and in the Word Template. Always with the same results.

I also added the following code to format the cell values:

Value = Format(DueDate, "dd MMMM yyyy hh:mm AM/PM")
Thank you

Lee

office-vba-devoffice-scripts-excel-dev
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe you can add a new column to ‘SELECT * FROM…’, which will transform strings like "01/07/2021 10:58:00 AM" to "07/01/2021 10:58:00 AM" using simple string manipulations that are available in SQL. This assumes that dates come from Excel as strings. Then use the new column in your fields.

Something like this: SELECT *, right(left(OldColumn, 5), 2) + '/' + left(OldColumn, 2) + substring(OldColumn, 6, 17) as NewColumn FROM…


0 Votes 0 ·

Viorel-1 I would prefer to understand why this is happening and find the correct solution, rather than using a workaround.

0 Votes 0 ·

0 Answers