Thread: [Solved] Using VBA to convert Excel serial date to Mail Merge Date format
View Single Post
 
Old 11-19-2025, 07:22 AM
Rudy Rudy is offline Windows 10 Office 2021
Novice
  
Join Date: Jul 2023
Location: Liverpool, England
Posts: 2
Rudy is on a distinguished road
Post Using VBA to convert Excel serial date to Mail Merge Date format
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?
Reply With Quote