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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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