VBA & Macro Problems

Use Word VBA to Find an Excel Name and Fill Document Fields

John Wilson Oct 20, 2026 868 views

Question details

The user needs to locate a specific name in an Excel spreadsheet using Word VBA and dynamically fill the corresponding data into Word document fields.

Use Word VBA to Find an Excel Name and Fill Document Fields

Product: Microsoft Word, Microsoft Excel
Device & OS: not provided
Scenario: Creating a Word template that prompts for a user's name, searches an external Excel database, and populates specific fields like Name, Age, and Score.
Observed behavior: Standard mail merge fields do not reliably update the visible document for single-person selections, requiring a switch to Content Controls and a VBA-driven UserForm.

Before you start

Ensure that the Developer tab is enabled in your Word ribbon and that you have the exact file path to your Excel data source ready.

Solution 1Recommended

Replace Mail Merge Fields with Content Controls via VBA

Using Content Controls instead of standard Mail Merge fields ensures that VBA can reliably write and update data for a single selected record.

Standard mail merge is designed for batch processing. For retrieving a single record based on user input, VBA interacting with Plain Text Content Controls is much more stable.

Insert Content Controls

In Word, go to the Developer tab. Delete existing mail merge fields and insert Plain Text Content Controls. Click Properties and assign clear titles like 'Name', 'Age', and 'Score'.

Open VBA Editor

Press ALT + F11 to open the VBA Editor, insert a new Module, and create a macro that opens the target Excel workbook using the correct file path.

Write the Search Loop

Write VBA code to prompt for a name, loop through the 'Full_Name' column in the specified Excel worksheet, and locate the matching row.

Populate Controls

Assign the values from the corresponding Excel columns to your Word Content Controls by referencing their titles, then close the Excel workbook.

Replace Mail Merge Fields with Content Controls via VBA

Check File Paths: Always verify that your VBA script contains the correct Excel workbook path, worksheet name, and exact Content Control titles to prevent runtime errors.

Solution 2

Create a Word UserForm with a List Box

A UserForm provides a graphical interface where users can select names from a dropdown list, preventing spelling errors that would break the VBA search.

Easy Mail Merge with WPS Office

Quickly Merge Excel Data into WPS Writer Documents

If writing complex VBA macros and building UserForms is too time-consuming, you can use the built-in Mail Merge wizard in WPS Writer. It allows you to quickly filter and select specific recipients from an Excel spreadsheet without any coding.

1. Start Mail Merge: Open your document in WPS Writer, navigate to the References tab, and click Mail Merge.
2. Connect Excel Data: Click Open Data Source and select the Excel spreadsheet containing your user names and scores.
3. Insert Merge Fields: Use the Insert Merge Field button to place the necessary fields (like Name, Age, Score) into your document layout.
4. Filter and View Results: Click Mail Merge Recipients to check or uncheck specific names. Click View Merged Data to instantly see the selected person's details.

Native support for Excel (.xlsx) data sources without VBA scripting.Intuitive Mail Merge Recipients interface to filter specific names.Fully compatible with Microsoft Office document formats.Lightweight and free to download.

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I use Mail Merge for a single VBA lookup?

Mail Merge is optimized for generating multiple documents or bulk emails. When using VBA to fetch a single record on the fly, Mail Merge fields can fail to visually refresh, making Content Controls a much more reliable target for VBA scripts.

How do I ensure my Word VBA macro doesn't misspell names?

Instead of using a simple InputBox for manual text entry, build a UserForm in Word with a ListBox or ComboBox. Populate this list directly from your Excel column so users can only select valid names.

Do I need special permissions to run macros between Word and Excel?

Yes, you must ensure that macro execution is allowed in the Trust Center settings for both Word and Excel. Additionally, you may need to add the Microsoft Excel Object Library in the Word VBA Tools > References menu if using early binding.

Excel & Data Problems