Home |
Search |
Today's Posts |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
date mailmerge problem
Some of the filtering is broken in more recent versions of Word because Word
sometimes generates the wrong SQL. Obviously if you are trying to hand over your sheet for someone else to use, this is a major pain. If you're just using it yourself the simplest option is probably to do your own separate step to filter in Excel (maybe create a new sheet and use the data facilities to do it). What I tend to do is create a macro that issues OpenDataSource and modify the SQL directly. For example Sub OpenExcelSource() ' This is a safety measure that disconnects the existing ' data source. You lose filters (including individual selections) and sorts ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument ' Create the merge type you need ActiveDocument.MailMerge.MainDocumentType = wdDirectory ' Open the source ' You may not need the SubType. ' Yes, I know it isn't an "Access" source, but it uses the Access/Jet OLEDB driver ActiveDocument.MailMerge.OpenDataSource _ Name:= "C:\mydata\myworkbook.xls", _ Connection:= "", _ SQLStatement:="SELECT * FROM `Sheet$` WHERE [mytextfield] = 'something'", _ SQLStatement1:="", _ SubType:= wdMergeSubTypeAccess End Sub Best I can do right now as I'm out of circulation for a while soon. Peter Jamieson "Bob Bedford" wrote in message ... "Peter Jamieson" a écrit dans le message de news: ... I can replicate some of this (i.e. enough to see why it might not work, as Word is simply displaying the internal representation of the date stored in Excel ) but as soon as I apply date formats to all the cells, Word gets it right. (Word 2003 SP2) Two suggestions: a. You can try changing the way that Word connects to Excel to DDE. You can do that by checking Word Tools|Options|General|"Confirm conversions at open", then go through the Select Data Source dialog again, select your Excel workbook, and select the DDE option when prompted. However, DDE is sometimes unreliable, and it can only see the first sheet in your workbook. b. Instead of using =page2!C12 and cells formatted as dates, use cells formatted as general or text and use =TEXT(page2!C12,"DD-MMM-YYYY"), substituting the format you want to use in Word in place of "DD-MMM-YYYYY" Peter Jamieson Thanks peter, the second option works like a charm. Anyway I'm having big troubles with mailmerge: selecting the fields I want (using the filter like column A is empty, column B isn't) I'm getting wrong results, and when I open the filter again, then the filters are shown twice or even three times. Any idea ? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i set a reacuring date on a document? | New Users | |||
Document Creation Date | Microsoft Word Help | |||
Mailmerge Datasourse Problem | Mailmerge | |||
problem printing date and other macros on word templates | Microsoft Word Help | |||
Date Field in Merge Document | Mailmerge |