Using VBA to convert Excel serial date to Mail Merge Date format

Rudy · #1 11-19-2025, 07:22 AM

Hi All,

I've had a dig around on the forum but haven't found a solution for my specific issue (At least not a solution I was hoping to go with.

I've got a spreadsheet I don't have permission to edit, but there's a cell [E7] on the [DOCVARIABLES] tab that contains a date I'm trying to pull into a letter.

This cell has the following formula and formatted as general (again, I can't change anything here)

Code:

=IFERROR(MainCalc!D3,0)

We have a macro in a word doc that takes this cell amongst others from the same tab and sets them up as mail merge variables. I've pasted an extract below

Code:

Dim DOR As String
Dim FlexPerc As String
Dim MaxPPen As String

DOR = oXL.ActiveWorkbook.Worksheets("Docvariables").Range("E3").Value
ActiveDocument.Variables("DOR").Value = DOR

FlexPerc = oXL.ActiveWorkbook.Worksheets("Docvariables").Range("E7").Value
If FlexPerc = "" Then
    FlexPerc = 0
    End If
ActiveDocument.Variables("FlexPerc").Value = FlexPerc

MaxPPen = oXL.ActiveWorkbook.Worksheets("Docvariables").Range("N82").Value
ActiveDocument.Variables("MaxPPen").Value = MaxPPen

FlexPerc I had to force a 0 value as the cell would be blank otherwise which results in an "ERROR! No Document Variable Supplied." error. All fine so far, but when I actually try to use the mailmerge, the DOR isn't formatted with the date switch.

I've got the switch marked as:

Code:

{ DOCVARIABLE  \@ "dd MMMM yyyy" }

I'm confident that's fine as it's worked in other documents that reference a different spreadsheet, so I'm sure the problem lies with the fact that the cell isn't formatted as a date.

I found a solution on the forum that suggest adding a block of code in the mergefield within {QUOTE } to convert a serial date, but I was wondering if I could do this within VBA to keep the document tidier?

Rudy · #2 11-19-2025, 08:17 AM

Turns out I didn't need anything too fancy, seems like VBA is happy enough with me forcing a date format like this

Code:

DOR = oXL.ActiveWorkbook.Worksheets("Docvariables").Range("E3").Value
DOR = Format(DOR, "dd/mm/yyyy")
ActiveDocument.Variables("DOR").Value = DOR

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail merge field mistakenly interpret text format as date format	alan6690	Mail Merge	0	09-02-2020 01:54 AM
Mail merge date format	Highlander01	Mail Merge	1	07-28-2016 05:05 PM
Convert csv document to excel, format date	coba	Excel Programming	3	01-07-2016 04:18 AM
Change Mail Merge Date Format from US to UK	Evanaught	Mail Merge	1	09-29-2013 08:02 PM
Mail merge will not format date field generated by Excel IF statement	borntorun75	Mail Merge	3	12-16-2011 06:28 AM