How to extract a zip file with vba

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
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.

Skill level:
Easy

Other People Are Reading

Instructions

  1. 1

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

  2. 2

    Type "Dim oApplicationlication As Object

    Dim filename As Variant
    
    Dim folderFileName As Variant" to declare variables.
    
  3. 3

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

  4. 4

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

    MkDir folderFileName

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

  5. 5

    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.

  6. 6

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

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"

Don't Miss

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.