Questions

Mail merge in Word 2007 using Excel data doesn't keep formatting.

Tags:
+
0 Votes
Locked

Mail merge in Word 2007 using Excel data doesn't keep formatting.

rbidels
I have a contact list as an Excel worksheet. The "Zip Code" column is formatted for "Zip+4). Looks good in Excel.

When I create a mail merge document in Word, the zip code is not formatted, (no hyphen.) I actually tried the same thing with a different file in Word 2003 and had the same problem.

Any ideas?

Thanks!
  • +
    0 Votes
    rbidels

    After perusing past posts I found one by "AtCollege" which pointed to the answer.

    I'll paste it below. Thanks AtCollege.

    -------------------------------------------

    Display Formatted #'s from Excel in a Word Mail Merge
    The solution starts in Word. Go to Tools, Options, General tab. Check Confirm Conversion at Open. OK.
    This is a permanent change in Word and you should only have to do it once.

    In Excel, format dates and numbers the way you want them to appear in Word.

    Back in Word's Mail Merge Wizard, Browse for the Excel recipient list. When it is opened, you will be prompted for the type of connection. Look for something with DDE in it. Once you select that, you will have to say what sheet, etc. to use. But then any fields which contain dates, numbers will display the formatting you chose in Excel.

    This "problem" started in Word XP, I think. Microsoft has a knowledge base article about it. Word 2007 also has the same "feature".
    Posted: 06/20/2007 @ 10:11 AM (PDT)

    AtCollege 1
    Job Role: Technical/PC Support
    Location: Elizabethtown, PA
    Member since: 08/03/2004

    +
    0 Votes
    AtCollege

    Word 2003's Confirm Conversion on Open worked well. Number formats from Excel came across during Word Mail Merge.
    Since WD2007 has the same kind of setting (Office Button, Word Options, Advanced, General section, check Confirm file format conversion on open)I thought it would work the same.
    But I am finding this is not so. When connecting to the recipient list, there is only one way to connect - ODBC. Without the DDE selection, formatting doesn't come across from Excel.
    I can supply field switches, but this doen't give me the right formatting. What should be 50% displays as 01%.
    My guess is that if I can get DDE as a choice when connecting to the data source, that the formatting will come across from Excel.

    +
    0 Votes
    AtCollege

    I applied the floating point calculation fix, a newer one and it fixed the problem. Here is what I used:
    http://blogs.msdn.com/excel/archive/2007/10/09/calculation-issue-update-fix-available.aspx
    Now my formats come across from Excel just fine.

    However Word 2007 makes getting the DDE type of connection harder to find. In the Confirm Data Source window, you have to check Show All, then scroll down near the bottom of the list to MS Excel Worksheets via DDE (*.xls)
    Yes, it will work with xlsx files too.

    +
    0 Votes
    Melissasiobhan

    For some reason when I am setting up the DDE connection as I used to with Office 2003 it keeps giving me an error message that it can not establish the DDE connection. Anyone have an idea?

    +
    0 Votes
    jimtanski

    I am experiencing the same problem and it is making me crazy! Did you learn anything because I did not see any comments. ANY help would be appreciated!

    +
    0 Votes
    AshishS

    I had the same issue but with dollar amount during mail merge from Excel to Word. After I saved the file as a CSV file and then pointed the mail merge to that, it got fixed.

    Worth a shot.

  • +
    0 Votes
    rbidels

    After perusing past posts I found one by "AtCollege" which pointed to the answer.

    I'll paste it below. Thanks AtCollege.

    -------------------------------------------

    Display Formatted #'s from Excel in a Word Mail Merge
    The solution starts in Word. Go to Tools, Options, General tab. Check Confirm Conversion at Open. OK.
    This is a permanent change in Word and you should only have to do it once.

    In Excel, format dates and numbers the way you want them to appear in Word.

    Back in Word's Mail Merge Wizard, Browse for the Excel recipient list. When it is opened, you will be prompted for the type of connection. Look for something with DDE in it. Once you select that, you will have to say what sheet, etc. to use. But then any fields which contain dates, numbers will display the formatting you chose in Excel.

    This "problem" started in Word XP, I think. Microsoft has a knowledge base article about it. Word 2007 also has the same "feature".
    Posted: 06/20/2007 @ 10:11 AM (PDT)

    AtCollege 1
    Job Role: Technical/PC Support
    Location: Elizabethtown, PA
    Member since: 08/03/2004

    +
    0 Votes
    AtCollege

    Word 2003's Confirm Conversion on Open worked well. Number formats from Excel came across during Word Mail Merge.
    Since WD2007 has the same kind of setting (Office Button, Word Options, Advanced, General section, check Confirm file format conversion on open)I thought it would work the same.
    But I am finding this is not so. When connecting to the recipient list, there is only one way to connect - ODBC. Without the DDE selection, formatting doesn't come across from Excel.
    I can supply field switches, but this doen't give me the right formatting. What should be 50% displays as 01%.
    My guess is that if I can get DDE as a choice when connecting to the data source, that the formatting will come across from Excel.

    +
    0 Votes
    AtCollege

    I applied the floating point calculation fix, a newer one and it fixed the problem. Here is what I used:
    http://blogs.msdn.com/excel/archive/2007/10/09/calculation-issue-update-fix-available.aspx
    Now my formats come across from Excel just fine.

    However Word 2007 makes getting the DDE type of connection harder to find. In the Confirm Data Source window, you have to check Show All, then scroll down near the bottom of the list to MS Excel Worksheets via DDE (*.xls)
    Yes, it will work with xlsx files too.

    +
    0 Votes
    Melissasiobhan

    For some reason when I am setting up the DDE connection as I used to with Office 2003 it keeps giving me an error message that it can not establish the DDE connection. Anyone have an idea?

    +
    0 Votes
    jimtanski

    I am experiencing the same problem and it is making me crazy! Did you learn anything because I did not see any comments. ANY help would be appreciated!

    +
    0 Votes
    AshishS

    I had the same issue but with dollar amount during mail merge from Excel to Word. After I saved the file as a CSV file and then pointed the mail merge to that, it got fixed.

    Worth a shot.