Forum Discussion
Can populate MS Word field codes with excel list?
Take this:
1. Prepare Your Excel File
Make sure your Excel file (ViolationList.xlsx) has:
• Column A: Violation Codes (e.g., 1.1, 1.2)
• Column B: Descriptions (e.g., "late to work", "absent")
2. Add a Macro to Word
Press 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 Word
Insert a bookmark named ViolationDescription where you want the description to appear.
4. Run the Macro
Hello 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.
Few things first, would like to mention in my excel document, both columns have headers if that’s relevant.
- 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.
So far I tried the formula replacing Sheets(1) with violation, it just keeps loading and does not do anything. Locks up word.
Hello 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.
Few things first, would like to mention in my excel document, both columns have headers if that’s relevant.
- 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.
So far I tried the formula replacing Sheets(1) with violation, it just keeps loading and does not do anything. Locks up word.
