VBA & Macro Problems

How to Batch Print Excel Files in Alphabetical Order Using VBA

Huda Qurayshi Sep 28, 2026 868 views

Question details

The user needs a method to batch print multiple Excel workbooks from a folder in alphabetical order using a VBA macro.

How to Batch Print Excel Files in Alphabetical Order with VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to print numerous Excel files located in a specific folder without having to open and print each one manually.
Observed behavior: Using the native File Explorer batch print feature often results in the files being printed out of alphabetical order due to varying processing times.

Before you start

Ensure all the Excel workbooks you want to print are placed in a single, dedicated folder. Verify that your default printer is online, properly configured, and has enough paper and ink before running the batch print macro.

Solution 1Recommended

Use a VBA Macro with Dir Function to Batch Print

This method uses VBA to loop through a specified folder, opening, printing, and closing each Excel file sequentially, which guarantees an alphabetical print order.

By utilizing the built-in VBA Dir function, you can retrieve file names in alphabetical order. The macro will process each file one by one, ensuring the printer queue receives them in the exact right sequence.

Open the VBA Editor

Launch a new Excel workbook and press Alt + F11 to open the Visual Basic for Applications (VBA) Editor.

Insert a New Module

In the VBA Editor, click on 'Insert' in the top menu and select 'Module' to create a blank script window.

Write the Looping Code

Write a script that uses the Dir function to enumerate all .xlsx or .xls files in your target directory. Assign the folder path to a variable, for example: FolderPath = "C:\Your\Folder\Path\"

Add Print and Close Commands

Inside the Dir loop, add code to open the workbook, print it using ActiveWorkbook.PrintOut, and then close it using ActiveWorkbook.Close False so changes are not saved.

Run the Macro

Close the VBA Editor, save your macro-enabled workbook, and press F5 or run the macro from the Developer tab to begin printing.

Use a VBA Macro with Dir Function to Batch Print

Folder Path Configuration: Make sure the folder path in your VBA script ends with a backslash (\) so the macro can accurately locate and concatenate the file names.

Solution 2

Modify the Macro to Print Only the First Page

If you only need the cover page or the first sheet of each workbook, modifying the VBA print command can save paper and time.

WPS Spreadsheet Solution

Automate Batch Printing with WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA macros, enabling you to automate tedious tasks like batch printing workbooks sequentially. With its highly compatible macro environment, running your existing automation scripts is seamless and efficient.

1. Install WPS Office: Download and install the free WPS Office suite on your device.
2. Open Developer Tools: Launch WPS Spreadsheet, navigate to the Developer tab, and click on 'VBA Editor'.
3. Paste and Run Your Macro: Insert a new module, paste your batch print VBA code, update the target folder path, and run the macro to print your files automatically.

Highly compatible with Microsoft Excel VBA macros and .xlsm file formats.Lightweight architecture for fast execution of folder-looping scripts.Intuitive Developer tab for easy macro creation and management.Free to use with a familiar interface that requires zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't Windows File Explorer print Excel files in alphabetical order?

When you select multiple files in File Explorer and click 'Print', Windows sends them to the printer spooler almost simultaneously. The final print order depends on how quickly Excel opens and processes each individual file in the background, which often leads to a randomized output rather than strict alphabetical sequence.

Can I batch print specific worksheets instead of the entire workbook?

Yes, you can easily modify the VBA code to target specific worksheets by index or name. Instead of using ActiveWorkbook.PrintOut, you can use Worksheets("SheetName").PrintOut to ensure only the designated tab is printed for each file in the folder.

Do I need to keep the spreadsheet application open while the macro is running?

Yes, the VBA macro requires the host application (like Microsoft Excel or WPS Spreadsheet) to remain open to execute the commands. The script will automatically open, print, and close the target workbooks in the background, but the application running the macro must stay active.

What happens if a file in the folder is password-protected?

If the macro encounters a password-protected file, the script will pause and prompt you to enter the password before it can proceed. To avoid this interruption during a batch job, you can either remove the passwords beforehand or add error-handling code (like 'On Error Resume Next') to skip files that cannot be opened automatically.

Excel & Data Problems