none
Mail Merge Field Limits RRS feed

  • Question

  • Hi,

    I have seen mentioned in the "Word Plug In - Mail Merge limit on fields" thread that there is no limit to the number of fields available if using Words mail merge functionality. The product I am working with inserts fields using the Office dll's, specifically Microsoft.Office.Interop.Word.DocumentClass.MailMerge.OpenDataSource(), can i please confirm that this is inclusive in that this is unlimited due to using Office's Mail Merge functionality to populate the list of fields?

    Further, our lowest supported version of Office is 2003, would you be able to advise me if there are any limitations in 2003 and later? - if so, what limitations are there?

    Many Thanks,

    Simon


    • Edited by SimonWhitty Wednesday, December 3, 2014 11:29 AM
    Wednesday, December 3, 2014 11:27 AM

Answers

  • AFAIK Starain is correct, i.e. Word 2003 is no different from later version of Windows Word in this respect, and in addition, it should not matter whether you are driving the Word object model using VBA or (say) .NET Interop. However, I would also check the specific situation, because it is always possible that a specific interface such as Interop will impose its own limitatons. (I am pretty sure it will not, in this case). There are also limitations in the number of columns that Word can present, as others have said.

    Other than that, the limit on the number of columns that Word can retrieve depends on the data source and the method that Word uses to get data from that source. Word uses SQL to get data from many types of data source What differs is the mechanism it uses and the dialect of SQL it uses.

    For example...

    Word even uses SQL to retrieve data from another Word document, but it is a very simple & limited dialect of SQL. I have no doubt that many people would even say "that's not SQL". Word uses Jet/ACE SQL to retrieve data from Jet/ACE sources such as Excel, Access, and in some case, plain text files. As far as Jet/ACE is concerned, 

    Excel 2003 only allowed 255/256 columns. So there was a maximum of 255/256 (I forget which).

    Excel 2007 allows a much larger number of columns, but none of the mechansims that Word uses to get data from Excel allows more than 255/256. - even OLE DB.

    AFAIK, that 255/256 limit applies to all Jet/ACE data sources, including the situation where you retrieve data from a SQL Server database via a Jet/ACE connection, the situation where you retrieve data from a text fiel using OLE DB.

    However, when Word gets data from SQL Server, the query needs to be in Transact-SQL, and the limitation is the Transact-SQL OLE DB provider (or the ODBC driver). AFAIK SQL Server itself supports thousands of columns. I cannot remember whether the OLE DB provider supports the maximum column count, or a smaller number such as 1023/1024. For other server-type databases such as Oracle etc., it's really any limitations in the driver/provider that you need to consider.

    A final limitation is that Word places a limit on the number of characters in the SQL statement that it issues - either 255 or 511 characters depending on the connection method. So iissuing a statement such as 'SELECT * FROM "some table"' is fine, but if you need "SELECT fielda, fieldb, some-expression AS fieldc FROM this table INNER JOIN thattable ON some criteria" you can run out of characters quite quickly - in that scenario you have to consider creating a View in your server database. (Word won't play nicely with a multi-statement TRANSACT SQL stored procedure, but you might be able to get a stored Table function to work.) 



    Peter Jamieson

    • Marked as answer by SimonWhitty Monday, December 8, 2014 10:20 AM
    Friday, December 5, 2014 7:38 PM

All replies

  • Hi Simon,

    As far as I know, it depends on the data source, the MailMerge.OpenDataSource method also need to specify datasource, for the data source of table in word, the limit is 62, for excel, the limit is 256.

    Please refer to these links:

    http://answers.microsoft.com/en-us/office/forum/office_2013_release-word/mail-merge-maximum-number-of-fields-2013/68c81d26-f4cb-42fd-9bc9-1a087b7b1dbd

    # How to design and set up a mail merge address list in Word

    https://support.microsoft.com/kb/294688?wa=wsignin1.0

    On the other hand, in my opinion, the limit is the same in office 2003 and later. You could test it to verify.

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, December 4, 2014 7:22 AM
    Moderator
  • Hi Starain,

    Apologies, I should have specified its an SQL query to populate the data with an Office Data Connection file.

    I'm not sure if this would fall under Excel with the 256 limit due to the ODC, or if it would have no limit due to only the connection information being passed to Word and the fields being collated using Words Mail merge functionality.

    You're right, i should test it out, its just difficult at the moment and (whilst i am being a little lazy too) i thought it might be quicker to jump on here.

    Simon



    • Edited by SimonWhitty Thursday, December 4, 2014 11:44 AM
    Thursday, December 4, 2014 9:39 AM
  • Hi Simon,

    Base on the link that I provide, there is this section:

    How many fields (pieces of information) do you have for each record (the collection of the pieces of information)?

    If you have a few pieces of information for each record, you can use the Mail Merge Wizard (on the Tools menu) in Word. This allows you to access the Mail Merge Recipients dialog box to set up your address list.

     There is a limitation of 255 fields in the Mail Merge Recipients dialog box. If your number of fields exceeds this value, do not use the Mail Merge Helper. Instead, use one of the following:

    • Use Microsoft Excel as your data source. The maximum number of data fields in an Excel worksheet is 256.
    • Use a Microsoft Access database as your data source.
    • Use a tab-delimited text file or a comma-delimited text file as your data source.

    Mail-merge performance degrades as the number of fields in the header row increases. The configuration of your computer and the available memory may also limit the number of fields that you can insert in your data document.

    The header record (row) cannot contain any spaces. Each header field is limited to 40 characters and must start with a letter. Each subsequent character must be a letter, a number, or the underscore character. All header fields must be filled in. Notice that the restrictions described here are different on some language Word versions.

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 5, 2014 2:29 AM
    Moderator
  • Thanks Starain.
    Friday, December 5, 2014 10:03 AM
  • AFAIK Starain is correct, i.e. Word 2003 is no different from later version of Windows Word in this respect, and in addition, it should not matter whether you are driving the Word object model using VBA or (say) .NET Interop. However, I would also check the specific situation, because it is always possible that a specific interface such as Interop will impose its own limitatons. (I am pretty sure it will not, in this case). There are also limitations in the number of columns that Word can present, as others have said.

    Other than that, the limit on the number of columns that Word can retrieve depends on the data source and the method that Word uses to get data from that source. Word uses SQL to get data from many types of data source What differs is the mechanism it uses and the dialect of SQL it uses.

    For example...

    Word even uses SQL to retrieve data from another Word document, but it is a very simple & limited dialect of SQL. I have no doubt that many people would even say "that's not SQL". Word uses Jet/ACE SQL to retrieve data from Jet/ACE sources such as Excel, Access, and in some case, plain text files. As far as Jet/ACE is concerned, 

    Excel 2003 only allowed 255/256 columns. So there was a maximum of 255/256 (I forget which).

    Excel 2007 allows a much larger number of columns, but none of the mechansims that Word uses to get data from Excel allows more than 255/256. - even OLE DB.

    AFAIK, that 255/256 limit applies to all Jet/ACE data sources, including the situation where you retrieve data from a SQL Server database via a Jet/ACE connection, the situation where you retrieve data from a text fiel using OLE DB.

    However, when Word gets data from SQL Server, the query needs to be in Transact-SQL, and the limitation is the Transact-SQL OLE DB provider (or the ODBC driver). AFAIK SQL Server itself supports thousands of columns. I cannot remember whether the OLE DB provider supports the maximum column count, or a smaller number such as 1023/1024. For other server-type databases such as Oracle etc., it's really any limitations in the driver/provider that you need to consider.

    A final limitation is that Word places a limit on the number of characters in the SQL statement that it issues - either 255 or 511 characters depending on the connection method. So iissuing a statement such as 'SELECT * FROM "some table"' is fine, but if you need "SELECT fielda, fieldb, some-expression AS fieldc FROM this table INNER JOIN thattable ON some criteria" you can run out of characters quite quickly - in that scenario you have to consider creating a View in your server database. (Word won't play nicely with a multi-statement TRANSACT SQL stored procedure, but you might be able to get a stored Table function to work.) 



    Peter Jamieson

    • Marked as answer by SimonWhitty Monday, December 8, 2014 10:20 AM
    Friday, December 5, 2014 7:38 PM
  • Thanks Peter and Starain,

    We have had > 256 columns accepted by word using our methods, but i couldn't understand why if there were an explicit limit due to table limitations. The last two paragraphs explain so much. Thanks very much.

    Simon

    Monday, December 8, 2014 10:22 AM