Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    MS Excel - MS Word file linking and merge questions

    Greetings!
    I am new to this forum having signed up only a couple of days ago after having read your comments posted on a YouTube tutorial that addressed using MS Word’s mail merge feature to merge multiple items or rows of data into one field. I became quite frustrated with the negative results and errors I encountered when I tried to apply the information shared in the video as well as the additional information and macro text posted on Microsoft’s technical assistance website. I am hopeful you may have some answers or recommendations for the issues I am encountering.
    I have a robust and complex Excel file (MS Excel 2010) that has been designed as a tool to be used by emergency managers conducting an assessment of a medical facility. This file has a tab for general data about the medical facility, multiple “worksheets” upon which the assessors will find the criteria to evaluate broken down into functions and activities, and a results tab into which all of the information on the worksheets is accumulated.
    In addition, I have an assessment report document set up as a mail merge in MS Word 2010. At present, it is set up as a “letter merge” with the merge fields positioned appropriately throughout the document to insert the appropriate text or figures into the report from the Excel workbook. There are also six sections of the report into which I need to merge four specific fields from the results tab of the workbook into separate formatted tables for each section. Lastly, there are five graphs in the report which draw from data in the workbook.
    Finally, this workbook and report comprise an “assessment toolkit” that I will distribute to over 250 users, many of which have very basic computer skills. My desired end result is to have the “tools” developed sufficiently that the user will simply need to download the Excel workbook and report document, conduct the assessment using the workbook to post results and information, and then open the report to have the report automatically populated (or merged) with the appropriate information.
    The issues I currently face that I would appreciate any insight your might offer in addressing include:
    1. How does one link the Excel workbook and Word report document such that when the two files are relocated (loaded onto an enterprise download site and subsequently downloaded by the users) the links are not lost? In other words, so long as the files are present in the same directory or folder and the names remain constant, they will remain linked.
    2. Is it necessary to merge the information into the report in two separate actions – a letter merge followed by a directory merge – in order to populate the appropriate fields or should this be set up as one merge? If the latter, please offer some recommendations on how to set this up. I have found that running two separate merges was feasible however when I set up the table merge I was not able to set up a directory merge to populate the tables needed in six sections of the report and, when I did complete the directory merge, MS Word created multiple copies of the 19-page report – one copy for every row of filtered data identified to merge. Consequently, a 19-page report was surpassing 900 pages in some instances!
    3. With regards to the Excel workbook, I would like to exclude some of the criteria from the assessment based on responses provided in the general information tab that indicate certain programs do not apply to a facility. My thought is to develop macros that will null the appropriate fields but I am also wondering if it is feasible to embed the macro in an “If” function (e.g., If(C5=”No”,”Not Applicable”,”Not Scored”) where the macro would change the multiple fields in the results tab that are related to the question answered in C5)?
    4. Related to the first question about linking the files, how does one set up the graphs to automatically update from the data posted in the Excel workbook when the report document is opened? My experience has been that these links are also quite easily lost.
    Looking back at what I have written, this is quite lengthy. If this is inappropriate for posting to for assistance please forgive me and let me know. However, any assistance or recommendations you may make will be greatly appreciated.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by Olsondnv View Post
    1. How does one link the Excel workbook and Word report document such that when the two files are relocated (loaded onto an enterprise download site and subsequently downloaded by the users) the links are not lost? In other words, so long as the files are present in the same directory or folder and the names remain constant, they will remain linked.
    A mailmerge requires an absolute path to the data source. Moving the mailmerge main document to a different folder doesn't change the path pointing to the data source. It's therefore necessary to update the path.
    Quote Originally Posted by Olsondnv View Post
    2. Is it necessary to merge the information into the report in two separate actions – a letter merge followed by a directory merge – in order to populate the appropriate fields or should this be set up as one merge? If the latter, please offer some recommendations on how to set this up. I have found that running two separate merges was feasible however when I set up the table merge I was not able to set up a directory merge to populate the tables needed in six sections of the report and, when I did complete the directory merge, MS Word created multiple copies of the 19-page report – one copy for every row of filtered data identified to merge. Consequently, a 19-page report was surpassing 900 pages in some instances!
    A letter merge can output the same information in multiple places. A directory merge would only be required where you have multiple records that you want to group together. I'm not sure what a label merge has to do with this. You need to provide more detail on what you're trying to do.
    Quote Originally Posted by Olsondnv View Post
    3. With regards to the Excel workbook, I would like to exclude some of the criteria from the assessment based on responses provided in the general information tab that indicate certain programs do not apply to a facility. My thought is to develop macros that will null the appropriate fields but I am also wondering if it is feasible to embed the macro in an “If” function (e.g., If(C5=”No”,”Not Applicable”,”Not Scored”) where the macro would change the multiple fields in the results tab that are related to the question answered in C5)?
    A word mailmerge can use IF fields for conditionally including/excluding/varying content in a document. No macros are required for this. If you're happy to use macros, however, an Excel macro could be used to drive the whole process, including pointing the mailmerge main document to the current folder.
    Quote Originally Posted by Olsondnv View Post
    4. Related to the first question about linking the files, how does one set up the graphs to automatically update from the data posted in the Excel workbook when the report document is opened? My experience has been that these links are also quite easily lost.
    Word mailmerges can't do Excel graphs. If the graphs are exported as image files in their own right, a mailmerge could import them into the document. The 'Tips & Tricks' sticky thread at the top of this forum shows how to do that - and includes a wealth of other information you might find useful.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. The Following User Says Thank You to macropod For This Useful Post:

    Olsondnv (2014-12-05)

  4. #3
    New Lounger
    Join Date
    Dec 2014
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you for the quick reply. I'll review and see if I can get some samples or screen shots posted to help illustrate.

    Much appreciated.

Tags for this Thread

Posting Permissions

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