VBA & Macro Problems

How to Extract Nested ZIP Files Using VBA

Adam Davis Oct 26, 2026 869 views

Question details

The user needs a VBA macro to automatically extract ZIP files that are nested inside other ZIP archives without duplicating code.

How to Extract Nested ZIP Files Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating the extraction of complex, multi-layered ZIP archives where standard extraction scripts only process the outermost layer.
Observed behavior: Standard VBA extraction code only handles the first layer, failing to extract archives nested within other archives.

Before you start

Ensure you have the Developer tab enabled to access the VBA editor, and verify that you have read and write permissions for the destination folders where the ZIP files will be extracted.

Solution 1Recommended

Use a Recursive VBA Procedure with Shell.Application

This flexible method handles multiple layers of nested ZIP files by calling the same extraction logic recursively whenever a new ZIP file is discovered.

A recursive procedure is ideal for this scenario because it can process an unlimited number of nested layers without requiring you to duplicate extraction code. The built-in Shell.Application object handles the ZIP extraction natively in Windows.

Open the VBA Editor

Press Alt + F11 to open the VBA editor. Right-click your project in the Project Explorer, select 'Insert', and click 'Module' to create a new script area.

Declare the Recursive Subprocedure

Create a new Sub procedure that accepts the ZIP file path as a parameter. It is crucial to declare the folder path variables as the 'Variant' data type, as the Namespace method requires it.

Initialize the Shell Object

Use 'CreateObject("Shell.Application")' to instantiate the Shell object. Use the Namespace method on the Shell object to target the ZIP file and the designated output folder.

Extract the Outer Layer

Use the 'CopyHere' method on the destination Namespace to extract the contents of the current ZIP file into its newly created directory.

Loop and Call Recursively

Use the 'Dir' function or 'FileSystemObject' to scan the newly extracted files. If any file ends with a '.zip' extension, call the exact same Sub procedure again, passing the new ZIP file's path as the argument.

Use a Recursive VBA Procedure with Shell.Application

Variant Data Type Requirement: When using Shell.Application.Namespace, the file path argument must be passed as a Variant. If you pass it as a standard String, the VBA code will throw an error.

WPS Spreadsheet Automation

Automate Tasks Efficiently with WPS Spreadsheet

WPS Office supports VBA (Visual Basic for Applications) in its Advanced and Pro editions, allowing you to run powerful recursive extraction macros seamlessly just like in Microsoft Excel.

1. Download and Install WPS Office: Get the latest version of WPS Office and ensure you have the edition that supports VBA.
2. Open WPS Spreadsheet: Launch WPS Spreadsheet and open a new or existing workbook.
3. Access the VBA Editor: Navigate to the Developer tab on the ribbon and click on 'VBA Editor' (or press Alt + F11).
4. Run Your Macro: Insert a module, paste your recursive Shell.Application ZIP extraction code, and run it directly.

Fully compatible with Microsoft Excel VBA macro syntaxLightweight architecture runs complex recursive scripts smoothlyFamiliar developer environment makes migrating code instantBuilt-in robust tools for advanced data automation

microsoft office alternative - wps office

Frequently Asked Questions

Why does the Namespace method fail with a Type Mismatch error?

The Shell.Application Namespace method specifically requires the folder path to be passed as a Variant data type. If your variable is dimensioned as a standard String, VBA will return a Type Mismatch or Object Required error.

Can this VBA macro handle more than two levels of nested ZIP files?

Yes, by utilizing a recursive procedure, the macro will automatically invoke itself every time it detects a new ZIP file within the extracted contents. This allows it to handle an infinite number of nested layers without modifying the code.

Does this Shell.Application method require external software like WinRAR?

No, the Shell.Application object relies entirely on the built-in ZIP extraction capabilities native to the Windows operating system, so no third-party archiving software is required.

Excel & Data Problems