Issue with mail merge date field

[Boog]

Running XP using Word and XL 2002.

I have an XL file with a column of dates formatted as "dd mm yyyy". When I
open a Word document and manually perform the mail merge, the date is merged
correctly in the format "dd mmm yy" (the merge format is different than the
cell format). When I programmatically mail merge, the date is merged as a
serial number. The date mergefield in the Word document has the switch
\@ "dd mmm yy" but the date is still merged as a serial number. I have
tried changing the XL cell format of the date to 'Date', 'General' and 'Text"
but it is still merged as a serial number. I have also tried to format (via
code) the specific merge datafield providing the date, but it is Read Only.

If it is of value, the code in the Word document to make it a mail merge
document is as follows. It runs when the file is opened:
(underscores were added since the code indentations were not maintained in
the post)

Sub SetupMailMerge()
On Error Resume Next
If ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument _
Then
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"H:\Office\Forms\Forms\Miscellaneous\Surgery Schedule.xls", _
ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", _
Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"DSN=Excel
Files;DBQ=H:\Office\Forms\Forms\Miscellaneous\Surgery _
Schedule.xls;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
, SQLStatement:="SELECT * FROM `'Surgery Schedule$'`", _
SQLStatement1:=""
ActiveDocument.Save
End If
End Sub

The code in the XL file that performs the merge is :

( Counter is previously dimensioned as Long, FileToOpen is dimensioned as a
String and PatientConsents is dimensioned as a dynamic string array )
....
For Counter = 1 To UBound(PatientConsents)
' Opening Consent Form
FileToOpen = "H:\Office\Progress Notes\Pre & Postop Forms\Consent _
Forms\PreOp Binder Consent Forms\" & PatientConsents(Counter, 3) &
".doc"
Set WordDoc = PreOpForm.Documents.Open(FileToOpen, ReadOnly)
PreOpForm.Visible = True
PreOpForm.Activate
' Setting correct record
With WordDoc
.MailMerge.ViewMailMergeFieldCodes = False
.MailMerge.DataSource.ActiveRecord = PatientConsents(Counter, 1)
.PrintOut
.Close
End With
PreOpForm.Visible = False
Next Counter

I have searched the XL Programming, Word MailMerge and Word programming NGs
but unable to locate a solution. The solution recommended most often is to
add \@ "a format here" to the mergefield but I am already doing that. I have
even tried opening the Word document without the ReadOnly attribute but there
is no change.

Again, the date merges with the correct format when performing the merge
manually (using the exact same files) but it fails when run via code ??? Any
suggestions would be greatly appreciated. Thank you.

Boog

 

[macropod]

Hi Boog,

I'm no expert at vba mailmerges, but:
I think the problem here is that you're trying to connect to Excel via
ODBC, which might be getting confused if there are different data types in
the column. You should be able to get around this with a DDE connection
(see 'OpenDataSource' in Word's help file).

Assuming your data come from a range named 'Surgery Schedule', try:

Sub SetupMailMerge()
Dim oSource As String
On Error Resume Next
With ActiveDocument
oSource = "H:\Office\Forms\Forms\Miscellaneous\Surgery Schedule.xls"
If .MailMerge.MainDocumentType = wdNotAMergeDocument Then
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=oSource, ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="Surgery Schedule" _
SubType:=wdMergeSubTypeWord2000
'The last line above avoids the prompt to select a table in
Word 2002 and
'forces Word 2002 to emulate Word 2000 behaviour (use DDE
for data access).
End With
.Save
End If
End Sub

If that doesn't work, try asking in microsoft.public.word.mailmerge.fields

Cheers