Zipping and Unzipping Files

Perhaps the most commonly used type of file compression is the ZIP format. Even Excel 2007 files are stored in the ZIP format (although they don't use the .zip extension). A ZIP file can contain any number of files, and even complete directory structures. The content of the files determines the degree of compression. For example, JPG image files are already compressed, so zipping such a file will have little effect on the file size .

CD-ROM  

The examples in this section are available on the companion CD-ROM. The files are named ˜zip files.xlsm and ˜unzip a file.xlsm .

Zipping files

The example in this section demonstrates how to create a ZIP file from a group of user -selected files. The ZipFiles procedure displays a dialog box so the user can select the files. It then creates a ZIP file named compressed.zip in Excel's default directory.

 Sub ZipFiles()     Dim ShellApp As Object     Dim FileNameZip As Variant     Dim FileNames As Variant     Dim i As Long, FileCount As Long '   Get the file names     FileNames = Application.GetOpenFilename _         (FileFilter:="All Files (*.*),*.*", _           FilterIndex:=1, _          Title:="Select the files to ZIP", _          MultiSelect:=True) '   Exit if dialog box canceled     If Not IsArray(FileNames) Then Exit Sub     FileCount = UBound(FileNames)     FileNameZip = Application.DefaultFilePath & "\compressed.zip"     'Create empty Zip File with zip header     Open FileNameZip For Output As #1     Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)     Close #1     Set ShellApp = CreateObject("Shell.Application")     'Copy the files to the compressed folder     For i = LBound(FileNames) To UBound(FileNames)         ShellApp.Namespace(FileNameZip).CopyHere FileNames(i)     Next i     'Keep script waiting until Compressing is done     On Error Resume Next     Do Until ShellApp.Namespace(FileNameZip).items.Count = FileCount         Application.Wait (Now + TimeValue("0:00:01"))     Loop     If MsgBox(FileCount & " files were zipped to:" & _        vbNewLine & FileNameZip & vbNewLine & vbNewLine & _        "View the zip file?", vbQuestion + vbYesNo) = vbYes Then _        Shell "Explorer.exe /e," & FileNameZip, vbNormalFocus End Sub 

Figure 27-11 shows the file selection dialog box generated by using the GetOpenFilename method of the Application object (see Chapter 12 for more information). This dialog box allows the user to select multiple files from a single directory.

Figure 27-11: This dialog box lets the user select the files to be zipped.

The ZipFiles procedure creates a file named compressed.zip and writes a string of characters , which identify it as a ZIP file. Next, a Shell.Application object is created, and the code uses its CopyHere method to copy the files into the ZIP archive. The next section of the code is a Do Until loop, which checks the number of files in the ZIP archive every second. This is necessary because copying the files could take some time, and, if the procedure ends before the files are copied , the ZIP file will be incomplete (and probably corrupt).

When the number of files in the ZIP archive matches the number that should be there, the loop ends, and the user is presented with a message like the one shown in Figure 27-12. Clicking the Yes button opens a Windows Explorer window that shows the zipped files (see Figure 27-13).

Figure 27-12: The user is informed when the ZIP file is complete.

Figure 27-13: This Explorer window shows the ZIP file contents.

Caution  

The ZipFiles procedure presented here was kept simple to make it easy to understand. The code does no error checking and is not very flexible. For example, there is no option to choose the ZIP filename or location, and the current compressed.zip file is always overwritten without warning.

Unzipping a File

The example in this section performs the opposite function of the previous example. It asks the user for a ZIP filename and then unzips the files and puts them in a directory named Unzipped , located in Excel's default file directory.

 Sub UnzipAFile()     Dim ShellApp As Object     Dim TargetFile     Dim ZipFolder '   Target file & temp dir     TargetFile = Application.GetOpenFilename _         (FileFilter:="Zip Files (*.zip), *.zip")     If TargetFile = False Then Exit Sub     ZipFolder = Application.DefaultFilePath & "\Unzipped\" '   Create a temp folder     On Error Resume Next     RmDir ZipFolder     MkDir ZipFolder     On Error GoTo 0 '   Copy the zipped files to the newly created folder     Set ShellApp = CreateObject("Shell.Application")     ShellApp.Namespace(ZipFolder).CopyHere _        ShellApp.Namespace(TargetFile).items     If MsgBox("The files was unzipped to:" & _        vbNewLine & ZipFolder & vbNewLine & vbNewLine & _        "View the folder?", vbQuestion + vbYesNo) = vbYes Then _        Shell "Explorer.exe /e," & ZipFolder, vbNormalFocus End Sub 

The UnzipAFile procedure uses the GetOpenFilename method to get the ZIP file. It then creates the new folder and uses the Shell.Application object to copy the contents of the ZIP file to the new folder. Finally, the user can choose to display the new directory.