VBA & Macro Problems

How to Replace Repeated Word Placeholders with Different Values Using VBA

Kushani Nimanthika Oct 7, 2026 869 views

Question details

The user wants to replace multiple occurrences of the exact same placeholder (such as "#") in a Word document with different, sequential values sourced from an Excel sheet or Word table using VBA.

How to Replace Repeated Word Placeholders with Different Values Using VBA

Product: Microsoft Word
Device & OS: not provided
Scenario: Automating document creation by substituting repeated identical placeholders with a sequence of unique data entries.
Observed behavior: The user needs a programmatic method to apply a list of replacement values sequentially to identical placeholder symbols, rather than replacing them all with a single value.

Before you start

Ensure your document contains the exact repeated placeholder symbol (e.g., "#") and verify that the number of replacement values in your Excel file or table matches the number of placeholders.

Solution 1Recommended

Use a Word VBA Macro to Read Replacements from Excel

Execute a VBA script in Word that opens an Excel workbook and sequentially replaces placeholders one by one using the wdReplaceOne property.

This method involves a macro that opens a specified Excel workbook, reads values from a column, and applies them sequentially to each placeholder in your Word document.

Open the VBA Editor

In Microsoft Word, press Alt + F11 to open the Visual Basic for Applications (VBA) Editor.

Insert a New Module

Click 'Insert' in the top menu, then select 'Module' to create a blank script window.

Write the VBA Code

Write a script that uses Word's Find object. Crucially, set '.Text = "#"', '.Wrap = wdFindStop', and use 'Replace:=wdReplaceOne' so only one placeholder is replaced per loop iteration.

Link the Excel File

Include code to open the Excel application and loop through the rows in column A of your worksheet until it reaches the first blank cell.

Update File Path and Run

Replace the placeholder Excel file path in your code with the actual path to your workbook, then press F5 to run the macro.

Use a Word VBA Macro to Read Replacements from Excel

Validate Values: Always validate the number of placeholders and replacement values before running the macro to ensure no placeholders are missed.

Solution 2

Source Replacement Values from a Word Table

Adapt the VBA macro to read replacement data directly from a table within the Word document itself, avoiding external Excel dependencies.

Replace Placeholders and Automate Tasks in WPS Office

WPS Writer supports robust VBA macros, allowing you to run scripts that replace repeated placeholders with data from WPS Spreadsheet seamlessly.

1. Open Document in WPS Writer: Launch WPS Office and open your text document containing the placeholders.
2. Access the Macros Tool: Go to the 'Tools' tab on the ribbon and click on 'Macros' to access the developer environment.
3. Paste the VBA Script: Insert a new module and paste your sequential find-and-replace VBA script.
4. Run the Macro: Execute the macro to automatically pull replacement values from your linked WPS Spreadsheet file.

Fully compatible with Microsoft Word VBA macros and .doc, .docx, .docm formats.Seamless integration between WPS Writer and WPS Spreadsheet for automated data lookup.Lightweight, fast, and free to download with a highly familiar user interface.

Frequently Asked Questions

Why does my macro replace all placeholders with the first value?

This happens if your macro uses 'Replace:=wdReplaceAll'. You must change it to 'Replace:=wdReplaceOne' and ensure '.Wrap = wdFindStop' is set so it only replaces one instance per loop iteration.

Can I use different placeholder symbols instead of "#"?

Yes, you can use any distinct string. Simply change the 'Find.Text' value in your VBA code to whatever string you are using in your document, such as "[Name]" or "***".

What happens if I have more placeholders than replacement values?

If your Excel list or Word table runs out of values before all placeholders are found, the macro will finish and leave the remaining placeholders in the document unchanged.

Excel & Data Problems