VBA & Macro Problems

How to Export Highlighted Word Text to Excel with VBA

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to extract all highlighted text from a Word document and export it sequentially into a new Excel worksheet using a VBA macro.

Product: Word, Excel
Device & OS: not provided
Scenario: Automating the extraction of highlighted key points or data from a Word document into an Excel spreadsheet for better data management.
Observed behavior: The intended behavior is for the macro to open the target Word file, find every highlighted text segment, write each one to Excel, cut the text from the original Word document, and then save and close Word.

Before you start

Before running the macro, confirm the exact file path of your target document and make a duplicate backup copy, as the script will permanently remove the highlighted text from the original Word file.

Solution 1Recommended

Use a Cross-Application VBA Macro in Excel

Run a VBA script from Excel that opens the Word application, searches for highlight formatting, and extracts the text into your active worksheet.

This solution utilizes COM automation, allowing Excel's VBA to control Microsoft Word. The code will loop through the document to locate highlighted text, transfer it into new rows in Excel, and then delete the highlight from Word to prevent duplicate extractions.

Open the VBA Editor

Open a new workbook in Excel and press ALT + F11 on your keyboard to launch the Visual Basic for Applications (VBA) Editor.

Enable Word Object Library

In the VBA Editor menu, go to Tools > References. Scroll down the list, check the box next to 'Microsoft Word Object Library', and click OK. This allows Excel to communicate with Word.

Insert the Macro Code

Navigate to Insert > Module from the top menu, and paste your custom VBA extraction code into the blank module window.

Update File Path and Run

Locate the file path string in the macro code (e.g., 'C:\Path\To\Your\Document.docx') and change it to the exact location of your backup Word document. Press F5 or click the Run button to execute the extraction.

Customizing the Code: If you are requesting a custom script from a developer, always provide a sanitized test Word file with dummy text and an example of the expected Excel output to ensure the code meets your specific formatting needs.

Advanced Automation Supported

Extract and Manage Data Efficiently with WPS Office

WPS Office provides robust VBA/Macro support in its professional versions, allowing you to run cross-application scripts and automate complex data extractions between documents and spreadsheets with ease.

1. Install WPS Office: Download and install WPS Office, ensuring you have the version that includes VBA support enabled.
2. Open the VBA Editor: Launch WPS Spreadsheet, create a new file, and press ALT + F11 to open the built-in VBA Editor.
3. Paste Your Script: Insert a new module and paste your document extraction macro, updating the document paths as needed.
4. Execute the Extraction: Run the macro to automatically pull highlighted text from your WPS Writer document directly into the spreadsheet.

Fully compatible with Microsoft Excel and Word macro files (.xlsm, .docm)Built-in VBA editor for writing, testing, and debugging custom automation scriptsLightweight architecture ensures high performance during heavy data extractionSeamless interface makes switching from Microsoft Office effortless

microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro return a 'User-defined type not defined' error?

This error typically occurs when the macro relies on Word-specific objects but the Word Object Library is not enabled. Open the VBA Editor, go to Tools > References, and check 'Microsoft Word Object Library'.

Can I extract the text without deleting it from the original Word document?

Yes. You can modify your VBA code to use the 'Copy' method instead of 'Cut' or 'Delete' when handling the found text ranges, leaving your original Word document completely unchanged.

How can I extract only a specific color of highlighted text?

By default, the find function in VBA looks for any highlight formatting. To target a specific color (like yellow), you must set the 'HighlightColorIndex' property in your VBA Find parameters to the corresponding wdColorIndex value.

Will this extraction macro work on a Mac?

VBA macros that rely on COM automation to interact between Excel and Word often face compatibility issues on Mac due to Apple's strict sandboxing rules. This method works most reliably on Windows environments.

Excel & Data Problems