How to extract a zip file with vba

Updated July 20, 2017

Knowing how to extract zipped files with Visual Basic for Applications (VBA) is good skill to have. A zipped file contains one or more compressed files and is commonly used to save hard drive space. A benefit of a zip file is that it can be transferred faster from one location to another. VBA is commonly used in Microsoft Office applications such as Excel, Access and Word and gives you the flexibility to automate common repetitive tasks.

Open Microsoft Excel and, under the "Developer Tab," click on "Visual Basic." Type "Sub filesToUnzip()" and press "Enter" to create a new sub.

Type "Dim oApplicationlication As Object

Type "fileName = Application.GetOpenFilename(filefilter:="Zip Files (.zip), .zip", MultiSelect:=False)" to open a file browser window and choose the zip file to unzip.

Type "folderFileName = "C:\UnzippedFiles" & "\"

MkDir folderFileName

Set oApplication = CreateObject("Shell.Application")" to create a destination folder for the unzipped files.

Type "oApplication.Namespace(folderFileName).CopyHere oApplication.Namespace(filename).items" to extract the zipped files. Type "MsgBox "You have extracted the zip files to C:\UnzippedFiles\", vbInformation" to display a message that your files extracted successfully.

Type "End Sub" if necessary. Run your new sub to extract files from your zipped file.


Your sub should look like this: "Sub filesToUnzip() Dim oApplicationlication As Object Dim filename As Variant Dim folderFileName As Variant fileName = Application.GetOpenFilename(filefilter:="Zip Files (.zip), .zip", MultiSelect:=False) folderFileName = "C:\UnzippedFiles" & "\" MkDir folderFileName Set oApplication = CreateObject("Shell.Application") oApplication.Namespace(folderFileName).CopyHere oApplication.Namespace(filename).items MsgBox "You have extracted the zip files to C:\UnzippedFiles\", vbInformation End Sub"

Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Jaime Avelar is a professional writer whose programming articles appear on various websites. He has been a software programmer since 2000. Avelar holds a Master of Science in information systems from the University of Texas at Arlington.