Can populate MS Word field codes with excel list?

Question

Hello, I am attempting to create a document for my team at work. To put it simply, I would like us to be able to type the violation code, and the relevant violation will auto-populate the Word document. For example, typing 1.1 would add "late to work"; 1.2 would be "absent," etc.

My progress so far:

I have made a "Text Field (Form Control)" and bookmarked it, naming it "ViolationCode". Here we will type 1.1, 1.2, 2.2, etc. I have decided to use Text Field as a Drop Down because it is limited to 25 options, and we have 71 violations. The simplest way I have found to do this is using {IF{REF Violation}="1.1" "late to work"}. This would work, but I would have to do it 70 more times for the other violations, one by one. Is there a faster way to complete this task?

Note: I have listed all the violations in an Excel Document, which I will refer to as ViolationList.xlsx. In column 1 is the code, and column 2 is the description.

kidd_ip · Answer

Take this:
&nbsp;
1. Prepare Your Excel FileMake sure your Excel file (ViolationList.xlsx) has:• &nbsp; &nbsp;Column A: Violation Codes (e.g., 1.1, 1.2)• &nbsp; &nbsp;Column B: Descriptions (e.g., "late to work", "absent")2. Add a Macro to WordPress Alt + F11 in Word to open the VBA editor, then paste this code:
Sub PopulateViolationDescription()
    Dim xlApp As Object
    Dim xlBook As Object
    Dim xlSheet As Object
    Dim code As String
    Dim desc As String
    Dim i As Integer

' Get the code from the form field
    code = ActiveDocument.FormFields("ViolationCode").Result

' Open Excel
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open("C:\Path\To\ViolationList.xlsx")
    Set xlSheet = xlBook.Sheets(1)

' Search for the code
    For i = 1 To xlSheet.Cells(xlSheet.Rows.Count, 1).End(-4162).Row ' xlUp
        If xlSheet.Cells(i, 1).Value = code Then
            desc = xlSheet.Cells(i, 2).Value
            Exit For
        End If
    Next i

' Insert the description into a bookmark
    ActiveDocument.Bookmarks("ViolationDescription").Range.Text = desc

' Clean up
    xlBook.Close False
    xlApp.Quit
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub
Replace "C:\Path\To\ViolationList.xlsx" with the actual path to your file.
3. Add a Bookmark in WordInsert a bookmark named ViolationDescription where you want the description to appear.
4. Run the Macro

bubbykong · Answer

Hello&nbsp; thank you for your reply. So I’m not too well versed in VBA coding. If you could clarify a few questions I have it would be greatly appreciated.&nbsp;Few things first, would like to mention in my excel document, both columns have headers if that’s relevant.&nbsp;For code = ActiveDocument.FormFields(“ViolationCode”).Result - as mentioned in OP, I am using a Text Field (Form Control). Is this the correct command for that? I am asking as I see in another part of the code it is used ActiveDocument.Bookmarks.Under Open Excel, I named my tab “Violation” should I replace Sheets(1) with Violation? or should I move my tab to the first position?For the bookmark mentioned where I want the description you called “ViolationDescription”. I used another Text Field (Form Control) and bookmarked it “ViolationDescription” as you mentioned. Just sharing what control I used.&nbsp;&nbsp;So far I tried the formula replacing Sheets(1) with violation, it just keeps loading and does not do anything. Locks up word.

bubbykong · Answer

Hello&nbsp; thank you for your reply. So I’m not too well versed in VBA coding. If you could clarify a few questions I have it would be greatly appreciated.&nbsp;Few things first, would like to mention in my excel document, both columns have headers if that’s relevant.&nbsp;For code = ActiveDocument.FormFields(“ViolationCode”).Result - as mentioned in OP, I am using a Text Field (Form Control). Is this the correct command for that? I am asking as I see in another part of the code it is used ActiveDocument.Bookmarks.Under Open Excel, I named my tab “Violation” should I replace Sheets(1) with Violation? or should I move my tab to the first position?For the bookmark mentioned where I want the description you called “ViolationDescription”. I used another Text Field (Form Control) and bookmarked it “ViolationDescription” as you mentioned. Just sharing what control I used.&nbsp;&nbsp;So far I tried the formula replacing Sheets(1) with violation, it just keeps loading and does not do anything. Locks up word.

Forum Discussion

Can populate MS Word field codes with excel list?

3 Replies

Resources