Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with mail merge date field
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 -- macropod [MVP - Microsoft Word] ------------------------- "Boog" wrote in message ... 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\Surg ery _ Schedule.xls;DriverId=790;MaxBufferSize=2048;PageT imeout=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail Merge issue please HELP | Excel Programming | |||
Mail Merge issue | Excel Discussion (Misc queries) | |||
Using a date field in mail merge | Excel Discussion (Misc queries) | |||
Using a date field in mail merge | Excel Discussion (Misc queries) | |||
How to filter on a date field during mail merge | Excel Worksheet Functions |