All's not 100% yet as I'm finding myself running into some issues around this still.
When I use the mailmerge direct from Word, everything works fine. The file conversions check straightened out the date formatting problem I had.
However, the problem persists when I launch the Word mailmerge from VBA in my Excel application. My Excel VBA application allows the user to select which report she wishes to use.
The code below does everything it's supposed to do, however, the end result is the dates back into decimal format. The same report run direct from Word, and no problems.
Thoughts? Solutions?
Code:
Option Explicit
Dim objword As Object
Dim odoc As Object
Dim odoc2 As Object
Const wdsendtonewdocument = 0
Const wdsendtoprinter = 0
Dim mypath As String
Sub merge()
Dim fname As String
Set objword = CreateObject("Word.Application")
objword.DisplayAlerts = True
fname = "u:\Sports13\Reports\FR\v8\" & Worksheets("Front").Range("I14")
Debug.Print fname
Set odoc = objword.documents.Open(Filename:=fname, ConfirmConversions:=True, ReadOnly:=False, addtorecentfiles:=False)
objword.Visible = True
odoc.Application.Visible = True
odoc.mailmerge.Destination = wdsendtonewdocument
odoc.mailmerge.Execute
Set odoc2 = odoc.Application.documents("Catalog1")
odoc.Close False
mypath = "u:\Sports13\Workorders\" & Format(Worksheets("varhold").Range("A1"), "ddd dd-mmm-yy")
If Len(Dir(mypath, vbDirectory)) = 0 Then MkDir mypath
odoc2.SaveAs mypath & "\" & (Worksheets("varhold").Range("A46").Value & "docx")
objword.DisplayAlerts = True
AppActivate "Microsoft Excel"
Set objword = Nothing
Set odoc = Nothing
Set odoc2 = Nothing
End Sub