none
Word mail merge enters "0" (zero) instead of text into merged table field RRS feed

  • Question

  • I have a VBA procedure that runs a mail merge using data from an excel spreadsheet to create invoices.  The mail merge basic document template ( .dotm) has a table with ten rows for items, but on the majority of occasions only one row is used.  When there are multiple items, the mail merge sometimes (but not always) shows a zero instead of the text in the “item description” column in the table in the “FormLetters” document for the second and subsequent items.  The first row is always correct and the other columns (serial number, cost, VAT etc) all show correctly for all items.  Frequently, when I do a re-run of the procedure, it works correctly and shows the correct text on the “FormLetters” document in all rows using exactly the same data source.  The only significant fact that I can see is that the “item description” is usually quite long – about 80 characters, using three rows.  However, the problem never manifests itself in the first row of the table, even with longer blocks of text.

                     As far as I can see the VBA element should not cause any problems; it is only a few lines long, simply activating the worksheet as a data source and commanding a mail merge into the template.  I have checked all the field names are correct, but because it is an intermittent fault I have no ideas left for analysis.  Is there as way to step through the mail merge procedure in a manner similar to using F8 in VBA?  Any other suggestions for how I can analyse this problem further would be much appreciated.

    Monday, September 24, 2012 8:05 AM

Answers

  • Changing the Excel cell format won't help. The simplest solution is to not mix data types; failing that, ensure the first data row or two in the affected column  have text content; and, failing that, try connecting via DDE.

    For further info, see: http://tips.pjmsn.me.uk/t0003.htm


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, September 24, 2012 11:50 AM

All replies

  • Without seeing the code, and perhaps some data and the document, diagnosis would be difficult.

    FWIW, you can use Word's Catalogue/Directory Mailmerge facility for this (the terminology depends on the Word version). To see how to do so with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
    http://lounge.windowssecrets.com/index.php?showtopic=731107
    or
    http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
    The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

    For some worked examples, see the attachments to the posts at:
    http://www.msofficeforums.com/mail-merge/9180-mail-merge-duplicate-names-but-different-dollar.html#post23345
    http://www.msofficeforums.com/mail-merge/11436-access-word-creating-list-multiple-records.html#post30327

    Alternatively, you may want to try the Many-to-One Mail Merge add-in from Graham Mayor at: http://www.gmayor.com/ManyToOne.htm


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, September 24, 2012 9:07 AM
  • Paul

    Many thanks for your reply.  I have been through the links that you suggested, and they seem to be mostly related to constructing mail merges.  What I really need is to see why, on some occasions, I get zero in a field instead of the text in the data source.  Do you know of any way of stepping through the mail merge record by record as the merge proceeds, seeing the contents of each data field as it is added.  I can, of course, do a preview of the results before finally doing "Finish & Merge", but by that time the damage seems to be done!

    Thanks

    Andy C

    Monday, September 24, 2012 10:22 AM
  • The links are because your own post refers to a mailmerge process. The last one is a pre-packaged Word addin you can install.

    Presumably your own code uses a loop to cycle through the records. In that case, you could use something as simple as a message box to report what each record is returning. The problem with that, though, is that it might mask any timing issues that, however unlikely, are related to the problem.

    One thing to be aware of with mailmerges is that, if you have a field (ie an Excel column) that has mixed data types (eg some numbers, some text), what can sometimes happen is that anything non-numeric will be reported as 0. I'm not sure if that's an issue here, though, as you seem to be referring to inconsistent results for tests with the same data. Indeed, it's not even clear why you'd use mailmerge for populating a document via an Excel macro-driven process.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, September 24, 2012 11:00 AM
  • Paul

    Thanks again.  The VBA reference is something of a red herring - I probably should not have mentioned it.  All it does is start the mail merge process.

    Sub MergeData()
        Dim Wd As Object 'MS Word programme
        Dim MergeDoc As Object 'Word document destination for merge
     
        Workbooks("InvoicePrintData.xlsm").Activate
        ActiveWorkbook.Save

        Set Wd = CreateObject("Word.Application")
        Set MergeDoc = Wd.Documents.Add("C:\Documents and Settings\Charles\My Documents\Chas\Ebay\Finance & Administration\eBay\Mail Merge\Invoice.dot")
        Wd.Visible = True

    End Sub

    However, your comment in your last paragraph may well be related to the problem, as the relevant cells in the excel workbook do indeed have a mix of text and numbers.  I will investigate that line further, perhaps starting by formatting the cells as text.  They are presently formatted as "General".  If that does not work I'll write a routine to get rid of the numbers.

    Thanks

    Andy C

    Monday, September 24, 2012 11:42 AM
  • Changing the Excel cell format won't help. The simplest solution is to not mix data types; failing that, ensure the first data row or two in the affected column  have text content; and, failing that, try connecting via DDE.

    For further info, see: http://tips.pjmsn.me.uk/t0003.htm


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, September 24, 2012 11:50 AM
  • Paul

    Once more, thanks.  There may be a way of getting the first couple of rows in the affected columns to be text, though regrettably the worksheet is wiped and re-generated with each new set of invoices.  Nevertheless, there must be a way to insert and then ignore a couple of dummy rows.  The link you provided looks very promising.  The DDE solution may not work as the relevant worksheet is the third in the workbook.  The next line of attack will then be to set the MERGEFIELD switches.

    Your advice has given me some lines to pursue now;  I was seriously stumped, so thank you.

    Andy C

    Monday, September 24, 2012 3:48 PM
  • Hi Andy,

    Mailmerge switches won't help where the problem is 0s appearing instead of text values.

    If you can sort the data before merging, that should enable you to have text data at the top. The mailmerge can sort the data differently to how it's sorted in the workbook.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, September 24, 2012 10:15 PM
  • Paul

    After following your link at http://tips.pjmsn.me.uk/t0003.htm I tried using the "Data|Text To Columns" procedure in excel in the column that was changing text to a zero during mail merge.  I also formatted the column as text to try to try to ensure that it was seen as text and not as a number.  Because the problem was an intermittent one I have waited a couple of weeks to see if it works;  so far it does, and we have had no recurrence of the problem.

    With thanks for your help

    Andy C

    Wednesday, October 10, 2012 7:32 AM
  • I have the same issue, using Office 2013 and mail merge excel data into a word document. I found the source of the error. My data colum is Zip Codes, and they import fine unless there is a change in the data format.

    In the example below my US Zip codes import fine, but Canadian zips (Combination of letters and numbers) show up as Zeros. 

    i.e. ZIP ==> 10021, 22211, 33222, ..., H5K23R

    The only way around this is to create a separate column and convert to text all numbers, and treat the column as text. Word reads the first line format and assumes the entire column has the same format.

    =TEXT(b2,"#####")  [Where B2 is where the data is at]

    It is a workaround but it was the only way I could fix this.

    Saturday, March 15, 2014 11:24 PM
  • Another fairly simple workaround is to either sort the data so at least some of the Canadian zip codes occur in the first 8 rows or to insert some dummy rows with text in them there. This, at least, doesn't require the creation of extra fields in the data source.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Sunday, March 16, 2014 1:45 AM
  • I have the same problem. Thanks for sharing. I´m not sure if it works with mine. I want the choice to have N/A or digits in the same column.  I have grades to insert and some students did not do that particular exam (absent), so I put N/A for them. If anyone can help me I would really appreciate it. Thanks.
    Thursday, December 24, 2015 5:03 PM
  • As you have the same problem, the answer of September 24, 2012 applies to your situation.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Friday, December 25, 2015 12:51 AM
  • We have found if we add another column and use formula like Left(A1,len(A1)) to bring the data into a consistent format type (text in this case) and then point to the new column instead of the original the mail merge functionality works again.
    Wednesday, August 23, 2017 2:31 PM
  • Even that won't help if what's in the source column is a mix of data types. To understand why, see Mailmerge Data Format Problems in in the Mailmerge Tips and Tricks thread at:
    http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
    or:
    http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, August 23, 2017 11:29 PM
  • hey thanks dear, I was getting the same error ("0") in mail merge with the help of your suggestion 'text to column' as 'TEXT' my issue is solved 

    really thanks 

    Friday, April 20, 2018 10:20 AM
  • I'll admit I still have an ancient version of Word.  My original merge document had always worked for a directory I need to update every couple years.  This year, it was not playing nice and put "0" (zeros) in a field meant to show a cell phone number.  I figured the data was probably formatted as a number and not text.  Nope.  It was formatted as text. Ugh.  I tried re-saving and reformatting over and over.  

    The only thing that eventually worked for me was saving the data file as a CSV (.csv) file (comma separated value) and doing the merge from that file!  Super fun!  

    Tuesday, September 11, 2018 7:13 PM
  • As I said in my replies of September 24, 2012 and Wednesday, August 23, 2017, the problem is with data in mixed formats; the cell format is of no consequence unless you're using DDE.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, September 12, 2018 12:51 AM