Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Bob Bedford
 
Posts: n/a
Default date mailmerge problem

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



  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default date mailmerge problem

How are you referencing the other cell?

Peter Jamieson

"Bob Bedford" wrote in message
...
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




  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default date mailmerge problem

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" wrote in message
...
simply
='page2'!C12


"Peter Jamieson" a écrit dans le message
de news: ...
How are you referencing the other cell?

Peter Jamieson

"Bob Bedford" wrote in message
...
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










  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default 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 ?




Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i set a reacuring date on a document? welker_daniel New Users 4 October 29th 05 08:38 AM
Document Creation Date Thomas M Microsoft Word Help 3 October 29th 05 04:02 AM
Mailmerge Datasourse Problem NZ JHL via OfficeKB.com Mailmerge 1 June 18th 05 10:21 AM
problem printing date and other macros on word templates Hitesh Microsoft Word Help 4 April 8th 05 04:49 PM
Date Field in Merge Document Karen Hart Mailmerge 35 March 29th 05 12:38 PM


All times are GMT +1. The time now is 12:56 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"