Import Excel Named Ranges into Word Bookmarks with VBA in WPS Office
Question details
The user needs a reliable VBA workflow to import Excel named range values into matching Word bookmarks while preserving bookmarks, correctly updating Word form fields, and handling document protection in WPS Office.
- Product
- WPS Office—WPS Spreadsheets and WPS Writer (VBA/macros)
- Device & OS
- not provided
- Scenario
- Automating data transfer from an Excel workbook that contains named ranges to a Word document that contains matching bookmarks and legacy form fields.
- Observed behavior
- Replacing bookmark text in the Word document removes the bookmark, legacy form fields require a different update method, and protected documents block editing unless unprotected first.
Confirm that the Excel named range names exactly match the Word bookmark or form field names, and identify whether each target is an ordinary bookmark or a legacy form field so the VBA code can use the correct update method.
Update Word form fields and ordinary bookmarks with separate VBA handling
Use this when the Word document contains both legacy form fields and ordinary bookmarks, because each target type must be updated differently to avoid losing the bookmark.
A bookmark is removed when its range text is replaced, so ordinary bookmarks must be recreated around the updated range. Legacy form fields should instead be updated through their Result property, which preserves the field and its associated bookmark.
In WPS Writer, open the Word document that contains the bookmarks and form fields, then press Alt+F11 to open the VBA editor.
In the VBA editor, click Insert > Module to create a new module for the import routine.
For each ordinary bookmark, capture its range with ActiveDocument.Bookmarks(name).Range, assign the named range value to the range text, and then call ActiveDocument.Bookmarks.Add with the same name and the updated range.
For each target that is a legacy form field, assign the named range value to ActiveDocument.FormFields(name).Result instead of replacing the bookmark range.
If the document is protected, unprotect it before the updates, then reprotect it afterward with the same protection type and password.
Unprotect the Word document before editing and restore protection after the VBA import
Use this when the Word document has editing restrictions, because VBA changes fail or prompt when protection is active.
Automate Excel Named Range to Word Bookmark Imports with WPS Office VBA
Use WPS Office VBA in WPS Spreadsheets and WPS Writer to read Excel named ranges and update matching Word bookmarks or form fields while preserving bookmarks and document protection.
- 1. Open the Excel and Word files in WPS Office: Open the workbook with named ranges in WPS Spreadsheets and the target document with bookmarks in WPS Writer.
- 2. Open the VBA editor: In WPS Spreadsheets or WPS Writer, press Alt+F11 to open the VBA editor and insert a module from Insert > Module.
- 3. Add the Word object library reference: In the VBA editor, click Tools > References and enable the Word object library so the macro can control WPS Writer or Word documents.
- 4. Run the import macro: Write or paste the routine that loops through named ranges and updates the matching bookmark or form field, then press F5 to run it and verify that bookmarks remain in the document.
Frequently Asked Questions
Why does replacing bookmark text remove the bookmark in Word VBA?
Because the bookmark is tied to the original range. After you set the range text, Word no longer has the old bookmark, so you need to call ActiveDocument.Bookmarks.Add with the same name and the updated range.
How do I update a Word form field instead of an ordinary bookmark?
Use the legacy form field's Result property instead of replacing the bookmark range. Assign the Excel named range value to the Result property of the matching ActiveDocument.FormFields entry.
Do I need to unprotect the Word document before the VBA import?
Yes. If the document is protected, unprotect it before writing to bookmarks or form fields, then reprotect it afterward with the same protection type and password.
Can I run this Excel-to-Word bookmark import in WPS Office?
Yes. Open the workbook in WPS Spreadsheets and the document in WPS Writer, press Alt+F11 to open the VBA editor, add the object library reference, and run the macro.
