VBA & Macro Problems

How to Use Excel VBA to Fill a Word Document from Worksheet Cells

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to automate the process of populating labeled placeholders in a Word proposal document using data from specific Excel worksheets (Pricing Worksheet and Alternates) via an Excel VBA macro.

Product: Excel
Device & OS: not provided
Scenario: Automating document generation by transferring dynamic pricing and alternate data from an Excel spreadsheet into a predefined Word proposal template.
Observed behavior: The user needs a script to open the Word document, locate placeholders, replace them with cell values (including the current date), and trigger this via a button in Excel.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon to access the VBA Editor, and verify that your Word document has clear, unique placeholders (e.g., <<Date>>, <<Price>>) ready to be replaced.

Solution 1Recommended

Use Excel VBA and Word Automation to Replace Placeholders

Write an Excel macro that opens a specific Word document and uses the Find and Replace method to swap placeholder text with cell values.

This method requires enabling the Microsoft Word Object Library reference in your Excel VBA editor. It gives you full control over the replacement process without using Mail Merge.

Enable the Word Object Library

Press Alt + F11 to open the VBA Editor in Excel. Go to Tools > References in the top menu and check the box for 'Microsoft Word Object Library'.

Initialize the Word Application

Insert a new Module and define a sub-routine that creates a new Word application instance using code like CreateObject("Word.Application").

Open the Word Template

Use the Documents.Open method within your VBA script to load your specific Word proposal template from its saved file path.

Execute Find and Replace

Create a loop or individual blocks using Word's Find.Execute method to search for your labeled placeholders and replace them with values referenced from ThisWorkbook.Sheets("Pricing Worksheet").Range("A1").Value.

Assign Macro to a Button

Go to the Developer tab in your Excel worksheet, click Insert, choose a Form Control button, and assign your new macro to it for one-click execution.

Macro Security: Make sure to save your Excel file as a Macro-Enabled Workbook (.xlsm) so your VBA code is preserved for future use.

Solution 2

Use Word Mail Merge as a No-Code Alternative

If VBA coding is too complex, you can use the built-in Mail Merge feature to link an Excel spreadsheet directly to a Word document.

Efficient Document Automation

Automate Word and Excel Tasks Seamlessly with WPS Office

WPS Office provides robust support for VBA macros and a highly intuitive Mail Merge feature. You can easily link WPS Spreadsheet data to WPS Writer documents to automate your proposal generation.

1. Prepare Data in WPS Spreadsheet: Open your data spreadsheet in WPS Spreadsheet and structure your pricing data clearly in columns.
2. Open Template in WPS Writer: Open your proposal document template in WPS Writer where the placeholders are located.
3. Link the Data Source: Navigate to the Mailings tab, click Open Data Source, and select your saved WPS Spreadsheet file.
4. Insert Fields and Merge: Highlight your placeholders, click Insert Merge Field, choose the matching data columns, and click Merge to New Document.

Full compatibility with Microsoft Excel (.xlsx, .xlsm) and Word (.docx) formatsBuilt-in Mail Merge wizard for easy data integration without codingSupport for advanced VBA macros to automate cross-application workflowsLightweight application that opens files quickly and efficiently

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'User-defined type not defined' error in my VBA code?

This usually happens if you haven't enabled the Word Object Library. In the VBA Editor, go to Tools > References and ensure 'Microsoft Word Object Library' is checked.

Can I format the Excel cell values, like currency or dates, when transferring them to Word?

Yes, you can use the VBA Format() function in your script before passing the value to the Word document. For example: Format(Range("A1").Value, "Currency").

Is it possible to trigger a Word macro directly from Excel?

Yes, using the Word Application object in Excel VBA, you can run a macro stored in the Word document by calling WordApp.Run "MacroName".

What if I don't want to use VBA at all?

The best alternative is to use the Mail Merge feature in Word. It allows you to link an Excel database to your Word document and automatically generate documents without any coding.

Excel & Data Problems