How to Count Characters in Multiple Word Documents Using Excel VBA
Question details
The user wants to automate the process of counting characters (excluding spaces) across multiple Word documents and list each file name along with its character count in an Excel workbook.
- Product
- Excel and Word
- Device & OS
- not provided
- Scenario
- Batch processing a folder containing multiple Word (.docx) files to retrieve and tabulate character counts without having to open each document manually.
- Observed behavior
- Users require a VBA script that utilizes Word automation to scan a directory, extract the character count, and write the data to Excel columns. When misconfigured, the macro may return a 'No DOCX files found' error.
Ensure that all target Word documents are saved with the .docx extension and are placed in the exact same folder where you intend to save your macro-enabled Excel workbook.
Create and Execute an Excel VBA Automation Macro
Set up a macro in Excel that loops through a specific folder, opens each Word document in the background, extracts the BuiltinDocumentProperties for character count, and logs the results in your worksheet.
This approach uses Word automation from within Excel. The script utilizes the 'Dir' function to find all .docx files in the workbook's directory, extracts the necessary metrics, and populates columns A and B with the file names and respective character counts.
Create a new Excel workbook and immediately save it as an Excel Macro-Enabled Workbook (.xlsm). It is critical that you save this file in the same folder that contains all your target .docx files.
Press Alt + F11 to open the VBA Editor. In the top menu, click Insert > Module to create a new module, and then paste your batch-processing VBA macro code into the blank window.
While still in the VBA Editor, go to Tools > References, scroll down the list, and check the box for 'Microsoft Word Object Library'. Click OK to apply. (Skip this step if your code uses Late Binding via CreateObject).
Close the VBA Editor. In your Excel worksheet, press Alt + F8 to open the Macro dialog box, select your newly created macro from the list, and click Run. Test this with a few files first before processing a large batch.
Automate Batch Character Counting with WPS Spreadsheet
WPS Office provides robust VBA and macro support, allowing you to run powerful automation scripts seamlessly. You can easily write, test, and execute macros in WPS Spreadsheet to batch-process multiple WPS Writer documents, saving time and significantly increasing your productivity.
- 1. Enable Macros in WPS: Open WPS Spreadsheet, navigate to the Developer tab on the top ribbon, and ensure that Macros are enabled for the current session.
- 2. Insert the VBA Module: Click on 'Visual Basic' or press Alt + F11 to launch the VBA editor. Go to Insert > Module and paste your document-scanning VBA code.
- 3. Execute the Automation: Save your spreadsheet as a macro-enabled workbook (.xlsm) in the target folder, press Alt + F8 to select your script, and click Run to extract the character counts instantly.
Frequently Asked Questions
Why does the macro count characters including spaces instead of excluding them?
The VBA property 'BuiltinDocumentProperties("Number of characters")' typically returns the count excluding spaces. If you need spaces included, you must change the property name in your code to 'Number of characters (with spaces)'.
Can I run this macro if my files are saved in the older .doc format?
Yes, but you will need to modify the file search filter in your VBA code. Change the line that looks for '*.docx' to '*.doc' so the 'Dir' function successfully locates those older files.
Why am I getting a 'User-defined type not defined' error when I run the code?
This error typically occurs if your code is using Early Binding (declaring variables as Word.Application or Word.Document) and you forgot to enable the Word Object Library. To fix it, open the VBA Editor, go to Tools > References, and check 'Microsoft Word Object Library'.
How can I make the macro search subfolders as well?
The standard 'Dir' function approach only searches the immediate directory. To search subfolders, you will need to update your VBA code to use the FileSystemObject (FSO) and implement a recursive function that loops through all subdirectories.
