none
Word (Letter) Template using specific cells from Excel documents RRS feed

  • Question

  • Hi

    I have a folder with 100 individual excel files (xlsm) where I want to target specific cells and populate into a word template.  The majority of the cells are coming from the sheet "cover".  Example of specific cells are d58, a21, c5, etc.  I do not know what is the best way to do this if all possible.  I need to know what I need on the Word template to recognize the cells I want.  I need to know how the template can produce 100 individual letters pulling from the 100 individual excel files.  I am thinking mail merge with some vb??

    Friday, August 5, 2016 4:29 PM

Answers

  • >>>The majority of the cells are coming from the sheet "cover".  Example of specific cells are d58, a21, c5, etc.  I do not know what is the best way to do this if all possible.  I need to know what I need on the Word template to recognize the cells I want. <<<

    According to your description, Word Mail Merge does not support to recognize the cells that specific cells are d58, a21, c5, etc. You could refer to How to automate Word with Visual Basic to create a Mail Merge, otherwise you could refer to below code or Record Macro to Mail Merge with Excel:
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:="D:\sampleData.xlsx", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=D:\sampleData.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Glo" _
        , SQLStatement:="SELECT * FROM `'Sheet Overall$'`", SQLStatement1:="", _
        SubType:=wdMergeSubTypeAccess
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    This workaround is that you could copy specific cells are d58, a21, c5 to Excel table.
    • Proposed as answer by David_JunFeng Thursday, August 11, 2016 2:00 PM
    • Marked as answer by David_JunFeng Tuesday, August 16, 2016 2:00 PM
    Tuesday, August 9, 2016 8:13 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Word, I'll move your question to the MSDN forum for Word

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=worddev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards

    Emi Zhang

    TechNet Community Support

    Please mark the reply as an answer if they help and unmark them if they provide no help.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.


    Monday, August 8, 2016 8:27 AM
  • >>>The majority of the cells are coming from the sheet "cover".  Example of specific cells are d58, a21, c5, etc.  I do not know what is the best way to do this if all possible.  I need to know what I need on the Word template to recognize the cells I want. <<<

    According to your description, Word Mail Merge does not support to recognize the cells that specific cells are d58, a21, c5, etc. You could refer to How to automate Word with Visual Basic to create a Mail Merge, otherwise you could refer to below code or Record Macro to Mail Merge with Excel:
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:="D:\sampleData.xlsx", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=D:\sampleData.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Glo" _
        , SQLStatement:="SELECT * FROM `'Sheet Overall$'`", SQLStatement1:="", _
        SubType:=wdMergeSubTypeAccess
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    This workaround is that you could copy specific cells are d58, a21, c5 to Excel table.
    • Proposed as answer by David_JunFeng Thursday, August 11, 2016 2:00 PM
    • Marked as answer by David_JunFeng Tuesday, August 16, 2016 2:00 PM
    Tuesday, August 9, 2016 8:13 AM