VBA & Macro Problems

How to Create a VBA Macro to Import Multiple Word Files into Excel

Kushani Nimanthika Oct 12, 2026 871 views

Question details

The user needs to extract specific data (such as names, keywords, and table values) from multiple identically structured Word documents and import them into separate rows in an Excel spreadsheet.

Create a VBA Macro to Import Multiple Word Files into Excel

Product: Excel, Word
Device & OS: not provided
Scenario: Automating data extraction from multiple standardized Word files to build a structured Excel database.
Observed behavior: Manual data entry is time-consuming and inefficient; the goal is to use a VBA script to loop through documents, parse the required values, and automate the data entry into Excel.

Before you start

Ensure all your source Word documents share the exact same structural template or formatting, and place them into a single, dedicated folder before running any macros.

Solution 1Recommended

Use a VBA Script to Loop and Extract Word Data

Create a custom VBA macro in Excel that systematically opens each Word document in a designated folder, locates the target text or tables, and writes the data into Excel rows.

To allow Excel VBA to manipulate Word documents, you must first enable the Microsoft Word Object Library reference in your VBA Editor. This enables Excel to use Word-specific objects like Documents and Tables.

Enable the Word Object Library

Open your Excel workbook and press ALT + F11 to open the VBA Editor. Go to Tools > References in the top menu, scroll down to check the box for 'Microsoft Word Object Library', and click OK.

Insert a New Module

In the VBA Editor, click Insert > Module. This will create a blank text area where you can write or paste your macro code.

Set Up the Folder Loop

Write a script utilizing the Dir() function to loop through your target folder. For example, assign a folder path string and use a Do While loop to iterate through all .docx files.

Extract the Data

Inside the loop, use Documents.Open to open the current file. Utilize the Find object to locate specific keywords, or reference tables using Tables(1).Cell(r, c).Range.Text. Assign these parsed strings to your Excel cells using ActiveSheet.Cells(row, col).Value.

Close and Advance

Ensure you close the Word document without saving (e.g., ActiveDocument.Close False) at the end of the loop iteration, increment your Excel row counter, and proceed to the next file.

Use a VBA Script to Loop and Extract Word Data

Test with a Sample Batch: Always test your new macro on a separate folder containing just 2 or 3 sample Word documents to verify your parsing logic before running it on hundreds of files.

Automate Cross-Document Data Processing with WPS Office

WPS Office fully supports VBA macros, allowing you to write, debug, and run custom scripts that automate data extraction from WPS Writer documents directly into WPS Spreadsheet.

1. Install WPS Office with VBA: Download and install WPS Office, ensuring you have the version that includes VBA support enabled for running custom macros.
2. Open the Developer Tools: Create a new blank Spreadsheet. Navigate to the 'Tools' tab on the top ribbon and click 'Developer Tools'.
3. Access the VBA Editor: Click 'Visual Basic Editor' to launch the coding environment. Insert a new module and paste your extraction code.
4. Run the Macro: Press F5 or click 'Run' to execute the script and automatically populate your spreadsheet with data from your Writer files.

Full compatibility with Microsoft Excel macro-enabled workbooks (.xlsm)Built-in Visual Basic Editor for writing complex cross-application automationLightweight application engine for processing large batches of text documents quicklySeamless integration between WPS Writer and WPS Spreadsheet

microsoft office alternative - wps office

Frequently Asked Questions

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

This error typically occurs if you declare Word objects (like Word.Application or Word.Document) without enabling the Word Object Library. Fix this by opening the VBA Editor, navigating to Tools > References, and checking the 'Microsoft Word Object Library'.

Can I extract data specifically from tables inside the Word documents?

Yes. If the data is stored in tables, you can target specific cells directly in VBA using syntax like: ActiveDocument.Tables(1).Cell(Row:=2, Column:=3).Range.Text. Just remember to strip out the end-of-cell markers (like carriage returns) that Word includes by default.

How can I let the user choose the folder location dynamically?

Instead of hardcoding the folder path, you can use the Application.FileDialog(msoFileDialogFolderPicker) method in your script. This prompts a standard folder selection window so the user can browse to the directory containing the Word files.

Excel & Data Problems