Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2001
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merge field formatting (2000 sr 2)

    I'm sure this is a simple problem, but it's driving me mad.

    I am exporting a information from a proprietary database in csv format.

    One of the fields I'm exporting is a zip code. The data is coming out in #####-#### format.

    I do some massaging of data in Excel, then do a mail merge to form letters in Word.

    The zip code field gets transformed into a numeric field and the - becomes a decimal point.

    I tried formatting the field in Excel as a zip code field with no change to the output in Word.
    I converted the field to a 5 digit zip code. Word puts a .0 on the end of the field.
    I formatted the 5 digit field into a "general" type. Same .0 on the end.
    I formatted the 5 digit field into a "text" type and got the .0 on the end.
    I created a single record version of the spreadsheet (the master file has 6000 records) and the formatting was perfect.

    I seem to remember that Access will do an auto-formatting thing for any file that has more than 10 records in a field. Am I falling victim to the same "feature" here?

    How do I get Word to bring the field in as I've formatted it in the spreadsheet?

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

    Re: Merge field formatting (2000 sr 2)

    What method do you use to connect to the Excel spreadsheet? If you check Confirm Conversions on Open in Tools | Options..., General tab, you will be presented by several options. If I choose Excel by DDE or Excel by Conversion, the data come in as formatted in Excel. If I choose OLE-DB Data Sources, I get the behavior you describe.

  3. #3
    Lounger
    Join Date
    Apr 2001
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge field formatting (2000 sr 2)

    Hans,

    Thanks for the follow up.

    I made the change under Options as you suggested.

    I was using ODBC to import the data into my mail merge. My options when I go to Get Data Source are:
    1) via converter
    2) via DDE
    3) via ODBC

    As you said, the formatting is still wrong under ODBC, but works under DDE.

    I had been using ODBC because my end users have "lite" PC's. They don't need to have the actual database file open on their PC's to extract the data, they just need a path to the data. DDE is very slow and is a resource hog as it requires the source data file to be open on the PC to extract the data.

    At this point, the speedier nature of the ODBC method is still better than DDE, even if the user has to manually alter the zip code field before printing the form letter.

    Is there a way to get this done under ODBC, or a better method under DDE?

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

    Re: Merge field formatting (2000 sr 2)

    I agree that ODBC is much "leaner" than DDE. I use it a lot for mail merges from Access.

    With ODBC, you have to provide formatted data in the source. In Excel, you could insert a column with formulas such as =TEXT(A2,"#####-####"). This column should be displayed correctly in Word.

  5. #5
    Lounger
    Join Date
    Apr 2001
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge field formatting (2000 sr 2)

    Hans,

    You're a prince! Thanks!!

    My focus on the formatting of the cell never allowed for the idea of just writing it into a formula. DUH!

    I knew I had to be missing something simple.

    Thanks again.

    -rich

Posting Permissions

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