How to extract a zip file with vba

Written by jaime avelar | 13/05/2017
How to extract a zip file with vba
Extract zipped files with Visual Basic for Applications. (binary flood image by Attila Toro from Fotolia.com)

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.

Tip

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"

Tips and warnings

  • 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"
Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.