Use Excel Fields to Populate Word Document

[JimmyC]

At my work, I am looking for a way to populate multiple word documents (Office 2010) with fields from Excel. We have fairly simplistic documents, where you need to drop the same name, address, customer number, etc. into multiple documents. One other item of importance is that my work place plans to transition to Office 2016 in early 2017---so I need a solution that can migrate, if possible, to the newest version of Office.

In my internet research, I have seen suggestions to use a mail merge and suggestions to use multiple bookmarks with links to an Excel worksheet. The bookmark suggestions were done in Office 2003 and Office 2007 so I am not sure that this even an alternative for Office 2010. To give an idea of size, we have about 15 documents and about 100 fields to insert in them. Some of the fields, like customer name, mailing address and customer number, will be used in all 15 documents. All fields will be used multiple times and my guess is that most fields will be linked to at least 3 documents.

I am looking at the Excel-to-Word route as my research in third party document assembly indicates that there is a one-to-one relationship--meaning there is a unique series of questions (answer file) to each Word document. In my case, that means you would need to type the customer name and address, etc. 15 times to create the 15 documents.

I am hoping some of the MS Word gurus have some general advice that will get me headed in the correct direction. We are willing to pay for a third party Word or Excel add-in to expedite this process---but I have not found any add-ins (i.e. paid or free) in my research.

Thanks
JimC

[HansV]

You can copy a cell in Excel and use Paste Special with the Link option in Word to create a link that will be updated automatically. This will work in all versions of Office.

S254.jpg

If you need to refer to the same information in multiple places in the same document, you can either use Paste Special in each location, or bookmark the first instance, and insert cross references to that bookmark in other places.

[Charles Kenyon]

I regularly use Mail Merge to do this in my legal practice. It allows me to prepare draft documents personalized for the client and case. http://www.addbalance.com/usersguide/mailmerge.htm

[JimmyC]

Hans & Mr. Kenyon--thank you both for the reply & web link. I am off to experiment and will be back if I need further assistance as I have no experience in this area. Thanks again! JimC

[JimmyC]

Hans,
Is there any ability to add "logic" to the paste special approach? I have learned how to edit the link--but it does not seem to permit any "logic". What I need, which I thought was simple and evidently is a false assumption, is to "read" a cell value in Excel, say a number 3 is in the cell and there is logic to say if there is a number 3, copy this cell. If it is a number 2, then copy this cell. Is there any way to add "simple" if/then logic? Thank you. JimC

[HansV]

That is possible - you can insert an IF field in the Word document. But it's far easier to build the logic into the Excel workbooks - use formulas to populate cells in a worksheet with the values that you need in Word. That worksheet could be hidden, so that ordinary Excel users don't even notice it. Formulas in Excel are much easier to create and maintain than formulas in Word.

[JimmyC]

Hans--thank you for the advice as it points me in the right direction. As always, I am appreciative of your advice and willingness to share your great talent and knowledge with others. JimC.

[JimmyC]

Hans,
Please see the screen shot below.

I have about 35 more fields to add to the document that I am working on and there will be approximately 40 link fields in total in the document when I finish it. My question is whether you know of any way to edit the links? Specifically, I have 20 more documents to link the 40 fields too--but all of the data is in the same row in the same worksheet in the same workbook and only the column changes. So I thought how easy it would be to complete the document that I am working on, save the document as a new filename and then edit the links table to change only the column information for all of the linked fields and I would be done much faster than manually inserting one field at a time!

This idea may be a stupid one--as I have generated quite a few of these in my lifetime, so please forgive me if my inquiry is outrageous.
Thank you. JIm

[HansV]

Does this work for you? You'll have to enter the current column number in the links (as shown in the screenshot - for R2C6 the column number is 6), and the desired new column number.

Code: Select all

Sub ChangeLinks()
    Dim OldCol As Long
    Dim NewCol As Long
    OldCol = Val(InputBox("Enter current column number"))
    NewCol = Val(InputBox("Enter new column number"))
    Dim fld As Field
    For Each fld In ActiveDocument.Fields
        If fld.Type = wdFieldLink Then
            fld.Code.Text = Replace(fld.Code.Text, "C" & OldCol & " ", "C" & NewCol & " ")
        End If
    Next fld
    ActiveDocument.Fields.Update
End Sub

[JimmyC]

Hans,
Sorry for some follow up questions. I just want to make sure I know "what to do". When I finish "linking" all of the fields in the document that I am working on, I will save it. Then I should save it again using a different file name. Using this "re-named, new" Word document, I then copy the code you provided into a module. Correct? Maybe I am just being over cautious---perhaps this is because I am now well into my third hour of working on this and dealing with all of the standard work interruptions. Hans, thanks again for your assistance and advice. JimC

[HansV]

I would store the macro in a module a separate document, not in the documents that you are processing.
Make sure that the document with the links to Excel is the active document, then switch to the Visual Basic Editor, click anywhere in the macro and press F5 to run it.

[JimmyC]

Hans,
Thank you for the additional code. I am still working through macro code to create additional documents as I am not sure what is wrong yet.

I do have another question as I seem to learn everything the hard way. I am under a bit of pressure to get a working demo together so I decided to copy the desktop folder on my office PC that contains the Excel file and the two Word documents with the linked fields to a flash drive. I did this to take them home to work on this project. I have the same version of Excel and Word (2010) on my home PC. I "incorrectly" thought that the links would work so long as the Excel file & Word documents were in the same folder---but this is incorrect as I have discovered that the "specific" path is saved in the excel-to-word link. Is this correct?

While I was bummed that I could not work on the project from home without changing all 80 links & even if I did change them, the links would then not work when I brought the flash drive back to work, my "bigger" concern is that there is a decent chance that the mapped drive that I was planning on hosting these on could be changed to another "letter" in the future by the network administrators without notice. It "looks" like to me, that even a change to the network drive letter will break the links. Is this correct and is there anyway that I could modify the links 'in bulk" should the network drive letter be re-assigned in the future? It seems that the "change source" button in Word does not permit a "bulk" change of all the links--that the "source" for each link must be individually updated. Is there an add-in that would "grant" me access to change the drive letter, etc. for all of the field links simultaneously?

Thank you again for all you do to help me--I greatly appreciate & trust your advice. Jim

[HansV]

Word MVP Paul Edstein (aka macropod) has written up a method to make the paths relative: Word Fields and Relative Paths to External Files. His example uses the INCLUDEPICTURE field, but you can apply it to the LINK field used by links to Excel.

[JimmyC]

Hans---I am truly amazed at your knowledge. I am off to check out the link and I would also thank Mr. Edstein for his assistance now. Hans, again thank you. Jim

[macropod]

Word MVP Paul Edstein (aka macropod) has written up a method to make the paths relative: Word Fields and Relative Paths to External Files. His example uses the INCLUDEPICTURE field, but you can apply it to the LINK field used by links to Excel.

Do note that the field code solution cannot be used with LINK fields.

[HansV]

Oops! Sorry about that...

[JimmyC]

Paul----thanks for that clarification! That explains why I haven't been able to make it work. I have just been grinding through making the additional links manually. JimC

[JimmyC]

Hans,
The code runs---but does not change the column reference. I do not get any error messages, but none of the column references are changed. I confirmed that the current document all references column 12 and the new data is in column 14. I have followed your suggestion and I have the document I wish to change the reference open and a separate document with the code stored in it. I hate to ask for help since I can't give you any tangible feedback as to why it is not working. Any suggestions? Thank you. Jim

Code: Select all

Sub ChangeLinks()
    Dim OldCol As Long
    Dim NewCol As Long
    OldCol = Val(InputBox("Enter current column number"))
    NewCol = Val(InputBox("Enter new column number"))
    Dim fld As Field
    For Each fld In ActiveDocument.Fields
        If fld.Type = wdFieldLink Then
            fld.Code.Text = Replace(fld.Code.Text, "C" & OldCol & " ", "C" & NewCol & " ")
        End If
    Next fld
    ActiveDocument.Fields.Update
End Sub

[HansV]

Does this version work for you?

Code: Select all

Sub ChangeLinks()
    Dim OldCol As Long
    Dim NewCol As Long
    OldCol = Val(InputBox("Enter current column number"))
    NewCol = Val(InputBox("Enter new column number"))
    Dim fld As Field
    For Each fld In ActiveDocument.Fields
        If fld.Type = wdFieldLink Then
            fld.Code.Text = Replace(fld.Code.Text, "C" & OldCol & Chr(34), "C" & NewCol & Chr(34))
        End If
    Next fld
    ActiveDocument.Fields.Update
End Sub

[JimmyC]

Hans--thanks. I will try first thing on Tuesday morning as I have an office issue to deal with now. I will let you know. Thank you again for all of your help. Jim