VBA & Macro Problems

How to Export Excel Hyperlink Data to Word using VBA

Amos Gikunda Oct 15, 2026 869 views

Question details

The user wants to use an Excel VBA macro to find active hyperlinks in a worksheet, copy the data from the worksheets they link to, and consolidate all the extracted data into a single Word document.

How to Export Data from Excel Hyperlinks to a Word Document with VBA

Product: Excel
Device & OS: not provided
Scenario: Automating the extraction of data from multiple linked worksheets and compiling the information into a single Word report via a script.
Observed behavior: The user needs a programmatic method to loop through Excel hyperlinks, copy the UsedRange of the target linked sheets, and paste them sequentially into Microsoft Word.

Before you start

Before running the macro, ensure that you have enabled the Microsoft Word Object Library reference in the Excel VBA editor so that Excel can communicate with and automate Word.

Solution 1Recommended

Use a VBA Macro to Export Linked Sheet Data to Word

Create and run a VBA script that automates Microsoft Word, loops through the hyperlinks on your active Excel sheet, and pastes the corresponding worksheet data into a new document.

This solution utilizes Word automation directly from within Excel. The macro identifies active hyperlinks, accesses the specific worksheet targeted by each link, copies the worksheet's used range, and transfers the data into a newly created Word document.

Enable the Word Object Library

Open the VBA Editor by pressing ALT + F11. Go to Tools > References in the top menu, scroll down, and check the box next to 'Microsoft Word XX.X Object Library' (the version number may vary). Click OK.

Insert a New Module

In the VBA Editor, click on Insert > Module to create a blank script window where you can write your macro.

Write the Automation Code

Create a Sub routine that initializes a new Word application instance. Use a 'For Each' loop to iterate through 'ActiveSheet.Hyperlinks'.

Copy and Paste Data

Inside the loop, write code to identify the hyperlink's target, use 'UsedRange.Copy' on the target worksheet, and then paste it into the Word Document using 'WordApp.Selection.Paste'.

Run the Macro

Close the VBA editor or press F5 while inside your Sub routine to execute the script. A new Word document will open containing the compiled data from your linked worksheets.

Check Hyperlink Destinations: Ensure your hyperlinks point to valid worksheets within the same workbook. External links or broken links will cause the copy operation to fail.

Automate Cross-Application Workflows with WPS Office

WPS Office provides robust support for VBA and Macros, allowing you to seamlessly extract data from spreadsheets and export it to documents within a single, unified application.

1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing your hyperlinks.
2. Access the VBA Editor: Navigate to the Developer tab on the ribbon and click on 'VBA Editor' to open the scripting environment.
3. Insert Your Macro: Go to Insert > Module, and paste your VBA code designed to export worksheet data to WPS Writer.
4. Execute the Code: Run the macro to automatically pull the linked data and generate your document.

Built-in support for VBA and macros to automate repetitive tasksFully compatible with Microsoft Excel formats including .xlsm and .xlsxUnified lightweight application combining Spreadsheet, Writer, and PresentationSeamless copy-pasting and formatting retention across applications

Frequently Asked Questions

Why am I getting a 'User-defined type not defined' error in my VBA macro?

This error usually occurs when the Microsoft Word Object Library is not enabled. In the VBA editor, go to Tools > References and ensure the box for the Word Object Library is checked.

Will this macro copy formatting along with the worksheet data?

Yes, when using the standard 'UsedRange.Copy' and paste commands in VBA, the default behavior retains the source formatting from Excel when pasting the data into Word.

How do I prevent the macro from failing on broken hyperlinks?

You can add error handling to your VBA script, such as 'On Error Resume Next' before the copy operation, or explicitly check if the target worksheet exists before attempting to copy its contents.

Excel & Data Problems