date mailmerge problem

[Bob Bedford]

Hello,

I'm trying to get a date field from an excel file using mailmerge.

In the Excel File, the column is a reference to an other cell on an other
page.
Both fields are in date format.

When merging in the Word document, the date becomes 38736

I've tried to merge formatting {MERGEFIELD datefield \@ "dd mmmm yy"} but it
doens't change anything.

I can't understand why it doesn't work.

Please help, as I really need this feature.

Bob

 

[Peter Jamieson]

How are you referencing the other cell?

Peter Jamieson

 

[Bob Bedford]

simply
='page2'!C12

How are you referencing the other cell?

Peter Jamieson

 

[Peter Jamieson]

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

 

[Bob Bedford]

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 ?

 

[Peter Jamieson]

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