Good morning.
I apologize, I am not very familiar with Office and VBA, if I write some mistake correct me. With Word 365 and Excel 365 (database), both same version, Italian, I am trying to manage a mail merge, where, for each Excel line, a document is generated and, through VBA code, a single docx file is created/save for each document. In VBA, for the automatic creation of the file name for each single docx, I am trying, through "DataFields", to read an Excel field called "Test Date", date format/category field, of the type 2012-03-14.
Here are the values of the 4 Excel rows, for that field:
| Test Date |
|---|
| 2025-12-13 |
| 2025-02-20 |
| 2025-02-01 |
| 2025-01-03 |
This is the VBA code I use to compose the file names:
Filename = .DataFields("Asset").Value & "_" & Format(.DataFields("Test_Date").Value, "dd.mm.yyyy")
Entire code:
End WithSub StampaUnioneSingoli_FILE()
' StampaUnioneSingoli FILE nel formato desiderato (docx, rtf, PDF, doc) Macro (di default è doc)
On Error GoTo ErrH
Dim objWdMailMerge As Word.MailMerge
Dim lngRecNum As Long
Dim strPath As String
Dim nomeFile As String
Dim nFileCompleto As String
Dim estensioneFile As String
Dim tipoFile As Integer 'Da settare, in base al tipo file che si vuole generare
tipoFile = 3 '1 per DOCX, 2 per RTF, 3 per PDF, 4 per DOC. Default DOC
Select Case tipoFile
Case 1
estensioneFile = ".docx" 'file docx
Case 2
estensioneFile = ".rtf" 'file rtf
Case 3
estensioneFile = ".pdf" 'file pdf
Case 4
estensioneFile = ".doc" 'file doc
Case Else
estensioneFile = ".doc" 'file doc
End Select
Application.ScreenUpdating = False
' Chiedi all'utente di selezionare la cartella di destinazione
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Seleziona la cartella per il salvataggio"
.AllowMultiSelect = False
If .Show = -1 Then
strPath = .SelectedItems(1) & "\"
Else
MsgBox "Nessuna cartella selezionata. Operazione annullata."
Exit Sub
End If
End With
'strPath = ThisDocument.Path & "\" 'Crea i file nella stessa directory del file Word di Stampa Unione
Set objWdMailMerge = ThisDocument.MailMerge
With objWdMailMerge
.Destination = wdSendToNewDocument
With .DataSource
.ActiveRecord = wdLastRecord
lngRecNum = .ActiveRecord
.ActiveRecord = wdFirstRecord
Do
.FirstRecord = .ActiveRecord
.LastRecord = .ActiveRecord
nomeFile = .DataFields("Asset").Value & "_" & Format(.DataFields("Test_Date").Value, "dd-mm-yyyy") 'compongo il nome dei singoli file in base al valore di Asset e Date
If Len(nomeFile) Then
nFileCompleto = strPath & nomeFile & estensioneFile
objWdMailMerge.Execute
With ActiveDocument
Select Case tipoFile
Case 1
.SaveAs nFileCompleto, wdFormatXMLDocument, AddToRecentFiles:=False 'file docx
Case 2
.SaveAs nFileCompleto, wdFormatRTF, AddToRecentFiles:=False 'file rtf
Case 3
.SaveAs nFileCompleto, wdFormatPDF, AddToRecentFiles:=False 'file pdf
Case 4
.SaveAs nFileCompleto, wdFormatDocument, AddToRecentFiles:=False 'file doc
Case Else
.SaveAs nFileCompleto, wdFormatDocument, AddToRecentFiles:=False 'file doc
End Select
.Saved = True
.Close
End With
End If
If .ActiveRecord = lngRecNum Then Exit Do
.ActiveRecord = wdNextRecord
Loop
End With
End With
MsgBox "Creazione dei file terminata."
ExitProc:
Application.ScreenUpdating = True
Set objWdMailMerge = Nothing
Exit Sub
ErrH:
MsgBox Err.Description
Resume ExitProc
End Sub
These are the file names, as a result:
SG1-45024353_13.12.2025.docx
SG1-00000000_20.02.2025.docx
SG1-00000000_02.01.2025.docx
SG1-45024353_01.03.2025.docx
I don't understand this operation. It seems as if the date field arrived in the DataFields anyway, in the text format "dd/mm/yyyy" and that for the dates "01/02/2025" and "03/01/2025", being valid also for the format "mm/dd/yyyy", in that case it kept them "good" as "mm/dd/yyyy", while for the other two "13/12/2025" and "20/02/2025", it tried to validate them as "mm/dd/yyyy" format, but giving an error, it then tried to consider them in the format "dd/mm/yyyy" and then kept those good.
If I'm not mistaken, .DataFields("Test_Date") reads directly from Excel and not from the value present in the mail merge result, in Word.
I don't know what else to check.
I expect to always receive the date in the same format.
I tried changing the language of Windows 11 to English, US, and the problem did not occur again. Unfortunately I cannot change the language for all the PCs where I will use the file. Is there any setting to force the Culture for Italian, ([IT; it])?
This problem is caused by the following:
By default, when Word connects to an Excel data source it uses the appropriate (Jet/ACE) OLE DB provider to get the data.
When Word uses any OLE DB provider to get MailMerge data, it returns date data in U.S. format, i.e. M/D/Y, regardless of your (Windows) system locale settings.
So let's say your system locale uses the data format D/M/Y, but your dates in Excel are formatted as YYYY-MM-DD, and you have the date 2025-02-01, i.e. 1st February 2025.
The date in Excel is stored internally in a binary date format. The OLE DB provider ignores the display format.
Word gets the date, using the OLE DB provider, and formats it in US format, e.g. 2/1/2025. It's just a piece of text now. If you display that using a MERGEFIELD field, e.g.
{ MERGEFIELD Test_Date }
you will probably see 2/1/2025 .
If you display the value of the field in VBA, e.g.
Debug.Print ActiveDocument.DataFields("Test_Date").Value
you should see 2/1/2025.
Word's "field language" 'knows' that the date is in US format, so if you do this:
{ MERGEFIELD Test_Date \@YYYY-MM-DD }
you will see 2025-02-01 .
But Word VBA sees a date string that it interprets according to the Windows locale, so if you do
Debug.Print Format(ActiveDocument.DataFields("Test_Date").Value, "YYYY-MM-DD")
you will see 2025-01-02 , i.e. the wrong date.
Both the Word "field language" and VBA will also correctly recognise a valid date in the "wrong sequence", i.e. if you have a Word bookmark called thedate containing the date 13/1/2025 (which would not be a valid US format date), then the result of
{ thedate @YYYY-MM-DD }
would be 2025-01-13 .
and Format("13/1/2025", "YYYY-MM-DD") would also be 2025-01-13 .
So in VBA, what you really need to do is to deal with the fact that the date is US format but there is no parameter to Format (or, say, CDate) that lets you specify the locale. Probably just use something like this:
Function MDY2YMD(datestring As String) As String
' datestring assumed to be in US *format* e.g. 1/2/2025 = 2nd January 2025
' It should always use the "/" delimiter
Const separator As String = "-"
Dim lb As Integer
Dim mdy As Variant
mdy = Split(datestring, "/")
lb = LBound(mdy)
If mdy(lb) > 12 Then ' deal with a date that is actually in D/M/Y format
MDY2YMD = Right("0000" & CStr(mdy(lb + 2)), 4) & _
separator & Right("00" & CStr(mdy(lb + 1)), 2) & _
separator & Right("00" & CStr(mdy(lb)), 2)
Else
MDY2YMD = Right("0000" & CStr(mdy(lb + 2)), 4) & _
separator & Right("00" & CStr(mdy(lb)), 2) & _
separator & Right("00" & CStr(mdy(lb + 1)), 2)
End If
End Function
The main problem with this is that if you then change the connection to your data so that it is not an OLE DB connection, the assumptions made will be the wrong ones.
This probably does not apply to your case, but I include it for completeness sake. If you are constructing any dates in the Word field language in x/y/z format, as I mentioned above, Word will interpret them as US format with an OLE DB connection, but as your locale's format without one.
So if your locale uses D/M/Y and you have fields like this
{ SET mydate "1/2/2025" }
{ mydate }
{ mydate \@YYYY-MM-DD }
in a non-Mailmerge document you will probably see
1/2/2025
2025-02-01
but if you connect to an OLE DB data source and re-execute the fields, you will probably see
1/2/2025
2025-01-02
(I say "probably" because there may be other settings that affect display that I haven't covered here.)
Personally, if I have to construct dates using field codes, I always use YYYY-MM-DD format because Word always seems to interpret that how you would expect, and I haven't seen a counterexample.