VBA & Macro Problems

How to Import Data from Multiple Word Files into Excel via VBA

Chanuka Geekiyanage Oct 10, 2026 872 views

Question details

The user needs to extract fields and tables from multiple similarly structured Word documents into an Excel spreadsheet using VBA, but may encounter OLE errors during automation.

Import Data from Multiple Word Documents into Excel Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating bulk data extraction from multiple structured Word files into a single Excel workbook.
Observed behavior: The automation process is complex and OLE errors frequently occur when the document object model or VBA code is incorrectly structured.

Before you start

Ensure that all target Word documents share the exact same layout or table structure before attempting to automate data extraction. Back up your files before running any new VBA scripts to prevent accidental data overwriting.

Solution 1Recommended

Structure the VBA Automation Code and Resolve OLE Errors

Properly referencing the Word application and correctly looping through files is essential to avoid memory leaks and OLE automation errors.

When importing data from Word to Excel, your VBA script must systematically open, read, and close each Word document. Failing to properly close documents or release objects in the loop often triggers OLE automation errors.

Enable the Word Object Library

Press ALT + F11 to open the VBA Editor in Excel. Navigate to Tools > References, and check the box for 'Microsoft Word Object Library' (or the equivalent WPS Writer library) to enable early binding.

Initialize the Word Application Object

In a new Module, create the Word application instance using code like: `Set wdApp = CreateObject("Word.Application")`. Keep `wdApp.Visible = False` to speed up the background extraction process.

Loop Through the Target Folder

Use the `Dir` function to loop through all `.docx` files in your specified directory. Within the loop, open each document using `wdApp.Documents.Open(Filename)`.

Extract Data and Clean Up

Extract the specific tables or form fields into your Excel workbook. Crucially, close each Word document at the end of the loop iteration using `wdDoc.Close False` and set objects to Nothing to prevent OLE errors.

Structure the VBA Automation Code and Resolve OLE Errors

Debugging Tip: If you receive an OLE error, use the F8 key in the VBA Editor to step through your code line by line. This will help you pinpoint the exact line where the Word automation fails.

Solution 2

Seek Expert Review on Developer Forums

If you cannot resolve complex OLE errors or layout extraction issues, getting help from VBA specialists is the most efficient next step.

Automate Data Workflows with WPS Office VBA

WPS Office provides robust VBA and Macro support in its professional editions, allowing you to seamlessly automate data extraction from WPS Writer documents into WPS Spreadsheets using familiar code syntax.

1. Open WPS Developer Tools: Launch WPS Spreadsheets and navigate to the Developer tab on the top ribbon.
2. Access the VBA Editor: Click on the 'VBA Editor' button to launch the integrated macro programming environment.
3. Insert Your Script: Go to Insert > Module, and paste your structured Word-to-Excel automation script.
4. Run the Automation: Press F5 or click the Run button to execute the script and automatically import your document data.

Fully compatible with Microsoft Excel VBA syntax and scriptsSupports OLE automation across Writer and Spreadsheets nativelyLightweight environment ensures fast execution of macro scriptsFamiliar developer interface makes debugging and coding easy

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an OLE automation error when running my VBA script?

OLE automation errors typically occur when the VBA code improperly references the Word application object, fails to release memory between file loops, or attempts to interact with a corrupted document. Ensuring you use 'Set Object = Nothing' and 'Document.Close' properly usually resolves this.

Do I need to enable specific references for Word-to-Excel macros?

Yes. If you are using early binding in your VBA script, you must open the VBA Editor, go to Tools > References, and check the 'Microsoft Word Object Library' (or equivalent) to use Word-specific objects and commands.

Can I extract data from Word documents into Excel without using VBA?

While VBA is the most efficient method for bulk extraction from hundreds of files, you can extract data without coding by using Power Query in Excel if the data is in structured text, CSV, or XML formats, rather than native Word tables.

Excel & Data Problems