VBA & Macro Problems

How to Create a Word Proposal from Excel Data Using a VBA Macro

Maira Mehtab Sep 22, 2026 871 views

Question details

The user needs an Excel VBA macro to automate generating a Word proposal by reading values from multiple worksheets, filling placeholders in a Word template, inserting the current date, saving the new document, and triggering the process via a worksheet button.

Product: Excel, Word
Device & OS: not provided
Scenario: Automating repetitive document generation by extracting spreadsheet data and injecting it into a pre-formatted word processing template.
Observed behavior: The user is looking for the correct VBA configuration to map source cells to template placeholders, customize file paths, and assign the automation script to an accessible button.

Before you start

Before writing the macro, prepare your Word template by inserting clear, unique placeholders (e.g., <<Date>> or <<ClientName>>) and note the exact file path where this template is saved on your computer.

Solution 1Recommended

Build and Customize the VBA Macro for Word Automation

Write a VBA script in Excel to open the Word template, replace placeholders with specific cell values, save the generated proposal, and link the script to a worksheet button.

Because this automation crosses between two different applications (Excel and Word), you must enable the Word Object Library in your Excel VBA environment. Your code will heavily depend on your specific workbook structure, so exact cell references and file paths must be customized manually.

Set up the Word template

Create a blank proposal in Microsoft Word. Wherever you need data from Excel to appear, type a distinct placeholder such as [Company] or [Date]. Save this document as a standard Word file (.docx) and copy its folder path.

Enable Word Object Library

In Excel, press Alt + F11 to open the Visual Basic for Applications (VBA) editor. Go to Tools > References in the top menu, scroll down, and check the box for 'Microsoft Word Object Library'.

Write the VBA script

Insert a new Module and declare your Word application variables. Write the code to open your specific template path (e.g., C:\Users\Desktop\Proposal blank.docx). Use a With statement to execute a Find and Replace loop, instructing the macro to locate your placeholders and replace them with the values from your target Excel cells.

Include date replacement and save commands

Ensure your Find and Replace loop also targets your date placeholders to replace them with the current VBA Date function. Finally, add a SaveAs2 command in the script to save the newly populated document under a new name, leaving the original template intact.

Assign the macro to a button

Close the VBA editor and return to your Excel worksheet. Go to the Developer tab, click Insert, and choose a Button from Form Controls. Draw it on your sheet, and when prompted, assign your newly created macro to this button.

Macro Customization Limitations: If you encounter errors when replacing placeholders across multiple worksheets, carefully check your sheet names and cell references. For highly complex automation involving nested tables or loops, consider seeking specialized code snippets on a VBA-focused programming forum.

Automate Document Generation in WPS Office

Use WPS Office to Run Excel Macros and Automate Proposals

WPS Spreadsheets includes robust Developer tools and macro support. You can easily adapt your Excel VBA scripts to pull data and generate WPS Writer documents seamlessly, all within a lightweight and highly compatible office suite.

1. Open your macro-enabled workbook: Launch WPS Spreadsheets and open your existing .xlsm file containing your proposal data.
2. Access the VBA Editor: Navigate to the Developer tab on the top ribbon and click on the 'Visual Basic' or 'Macro' icon to open the editor.
3. Customize your template paths: Paste or edit your macro script, ensuring the file paths reflect the location of your WPS Writer templates.
4. Run your automation via button: Insert a shape or form control button in your spreadsheet, right-click, select 'Assign Macro', and click it to instantly generate your proposal.

100% compatible with Microsoft Excel (.xlsx, .xlsm) formats and macros.Built-in Developer tools for macro recording and VBA editing.Lightweight software that runs smoothly on older devices without lagging.Seamless integration between WPS Spreadsheets and WPS Writer for data extraction.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my macro replacing all the date placeholders in the Word template?

Ensure your VBA Find and Replace command is set to replace all instances. In the Word VBA object model, you must include the argument 'Replace:=wdReplaceAll' so it doesn't stop after finding the first occurrence.

How can I find the correct file path for my Word template?

Navigate to the template file in Windows File Explorer, right-click the file, select 'Properties', and copy the path listed under 'Location'. Remember to append the exact file name and extension (e.g., \Proposal blank.docx) at the end of the path in your VBA code.

Can I run this macro if Microsoft Word is not installed on my computer?

No. If your VBA script explicitly relies on the Microsoft Word Object Library to manipulate a .docx file in the background, you must have Word installed on the machine running the macro. Alternatively, you can adapt the macro for compatible suites like WPS Office.

How do I prevent the macro from overwriting my original template?

In your VBA code, do not use the standard 'Save' command on the active document. Instead, use the 'SaveAs' or 'SaveAs2' method and specify a new file name and directory string so a brand new document is created.

Excel & Data Problems