Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Location
    California
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates in Merge (WordXP, SP2, Windows W2k SP3)

    Client has a 2-page merge document in Word. Data source is in Excel XP, SP2. Dates in data source are displayed as June 20, 2003. When document is merged it displays 06/20/2003. Verified that the cell formats are correct and fields are inserted properly. Any clues?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    Display field codes (Alt+F9). Insert format switches into the merge fields displaying dates:

    { MERGEFIELD MyDateField @ "MMMM d, yyyy" }

    Hide field codes again (Alt+F9).

    <table border=1><td align=center>Code</td><td align=center>Result</td><td align=center>d</td><td align=center>5</td><td align=center>dd</td><td align=center>05</td><td align=center>ddd</td><td align=center>Thu</td><td align=center>dddd</td><td align=center>Thursday</td><td align=center>M</td><td align=center>6</td><td align=center>MM</td><td align=center>06</td><td align=center>MMM</td><td align=center>Jun</td><td align=center>MMMM</td><td align=center>June</td><td align=center>yy</td><td align=center>03</td><td align=center>yyyy</td><td align=center>2003</td></table>

  3. #3
    Star Lounger
    Join Date
    Oct 2001
    Location
    California
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    Thanks! It worked like a charm. However, do you have any suggestions for Currency. Again I have selected the cells, format to currency. The document continues to place numbers, with no commas. I have added the dollar sign at the begining of each currency field.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    To format a merge field as currency, use the same method, but instead of the @ "..." switch meant for date/time fields, use # "$ 0.00". The # switch is for number/currency fields.

  5. #5
    Star Lounger
    Join Date
    Oct 2001
    Location
    California
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    It didn't work for the amount. I could not get dollar sign unless it was manually inserted. That's ok. The comma's would not carry over. There are no cents in the currency data only 100,000 and up. I tried custom fields, general, currency and plain text (no formatting).

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    The formatting in Excel is not important. Just as with the date field, you must do the formatting in the MERGEFIELD in Word. This works for me: { MERGEFIELD "Amount" # "$ #,##0" }

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    Hi Hans:
    I don't know if it still works in Word 2002, but in Word 2000, you can bring over the Excel formatting by deleting the word "mergefield" from the mergefield. Word will complain that the bookmark can't be found, but will still complete the merge. Caveat: you must have at least one mergefield in your main document that keeps the word "mergefield". I got this tip off of Cindy Meister's site.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    Phil.

    Deleting MERGEFIELD works in my Word 2002 (SP-2) in the sense that the data are still read from the Excel worksheet (didn't know that, interesting tip), but the formatting is not brought over from Excel. I created a workbook with numeric fields formatted different ways (numeric, percentage, currency) and they were all displayed in Word as "plain" numbers, both by { MERGEFIELD "FieldName" } and by { "FieldName" } fields.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    <P ID="edit" class=small>(Edited by Phil Rabichow on 25-Jun-03 18:17. to add attachment. I forgot that you can't wish attachments to the list [laugh])</P>Hi Hans:
    Word 2002 must be different. Too bad. I wonder if you can test the spreadsheet & main document that I attached. I'm curious as to what Word 2002 will do with documents already created. I can bring the numbers across in Word 2000 with the same formatting as the Excel spreadsheet.
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    Very strange; your Excel file works, mine doesn't. The preview dialog in which you can select which records to merge shows formatted data for your worksheet, and plain numbers for mine... I have attached my workbook, created in Excel 2002 SP-2.
    Attached Files Attached Files

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    Very strange. I had no trouble bringing across the formatting from Excel, just by deleting the word "mergefield" from the amount mergefield. I wonder if my Excel 2000 or Word 2000 somehow changes things, though I don't see how.

    I then made a couple formatting changes to the spreadsheet & tried again & they still came across. I've attached your "changed" spreadsheet & my form letters.
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    Hi Phil,

    Apparently modifying my spreadsheet in Excel 2000 doesn't help - formatting still doesn't come across (on a different XP system this time)

    <table border=1><td colspan=2 rowspan=2>Formatting brought over?</td><td colspan=2>Spreadsheet created in</td><td>Excel 2000</td><td>Excel 2002</td><td rowspan=2>Used in</td><td>Word 2000</td><td>yes</td><td>yes</td><td>Word 2002</td><td>yes</td><td>no</td></table>

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    Thanks again, Hans. Do you know if Word 2002 uses DDE or ODBC for mail merge? Word 2000 uses DDE, unless you specify otherwise. I believe that in the past, I got better results by making sure that Tools/Options/General/Confirm conversions on open is checked. If I then choose Excel by DDE or Excel by Conversion, the data came in as formatted in Excel. If you choose OLE-DB Data Sources, I got the behavior you describe.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    Word 2002 uses OLE-DB by default, and that's what I used. Excel by conversion didn't bring over formatting either, but DDE does - even without removing MAILMERGE from the field code! I haven't checked ODBC, for it presents a dialog with a combo box to select a file; the dropdown list shows only part of the path, so I can't find the spreadsheet.

    Anyway, mail merge using DDE brings over the formatting - good to know, thank you for making me try it!

  15. #15
    Star Lounger
    Join Date
    Oct 2001
    Location
    California
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in Merge (WordXP, SP2, Windows W2k SP3)

    Hi Hans,
    I was able to use the switches for MS Excel minus the 0 at the end. However, I had to use the 0 when the data source was in MS Access. Thanks for all your help. You saved me!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •