Working with Compressed (zipped) folders in MS Access
August 26, 2009
I'm sure you're all familiar with Zip files, and know that newer operating systems (Windows XP and newer) have the ability to work with Zip files built into them. Are you aware, though, that if you're working in one of those newer operating systems, you can work programmatically with Zip files simply by using the Windows Shell User Interface, without requiring any third party product?
The Microsoft Windows user interface has a large number of different objects in it to allow users much flexibility. The most numerous of these objects are the folders and files that reside on the conputer's disk drive, and the Windows Shell providers a consistent way to access these objects.
The Shell object represents the objects in the Windows Shell. The methods exposed by the Shell object can be used to do such things as
You're presumably familiar with the commands you can access from the Start menu and the taskbar's shortcut menu: all that functionality is exposed through the Shell object.
As you can well imagine, the Windows Shell is a large and complicated topic, and I'm only going to be discussing one small part of it. If you want more information about the Shell, start at what's in MSDN at http://msdn.microsoft.com/en-us/library/bb773177(VS.85).aspx
What is a Zip file?
A ZIP file contains one or more files that have been compressed to reduce file size (although the file format also allows files to be stored "as-is"). The ZIP file format permits a number of different compression algorithms to be used, with the dominant method (and the one used by Windows) being the Deflate method.
To some extent, referring to a "Zip file" is incorrect: from the perspective of the Windows Shell object. To Windows, a Zip file really is not different from a folder, since it can contains multiple files. In fact, as Figure 1 illustrates, when you right-click on a file and select Send To, Windows Shell actually presents the option of zipping the file as "Compressed (zipped) Folder"
Figure 1: The Send To menu Windows Shell gives a choice of "Compressed (zipped) Folder"
Since to Windows Shell, it's simply a folder, you can use the NameSpace method of the Shell object to create a Folder object that points to the contents of Zip file, just as it's possible to point to the contents of any other folder.
Now, I've read through the official .ZIP File Format Specification (http://www.pkware.com/documents/casestudies/APPNOTE.TXT) and I have to confess that it didn't help me understand how the method I'm about to demonstrate works. Hopefully, like me, you're willing to accept this as magic!
It turns out that what causes the Windows Shell to treat a Zip file as a folder, is the presence of a specific twenty-two byte "fingerprint" at the very beginning of the file. These twenty-two bytes consist of the following ASCII characters: Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0).
The code required to designate a new file as a Zip file, then, is presented as Sample 1.
Private Sub InitializeZipFile( _ ZipFile As String _ ) Dim intFile As Integer If Len(Dir(ZipFile)) > 0 Then Kill ZipFile End If intFile = FreeFile Open ZipFile For Output As #intFile Print #intFile, Chr$(80) & Chr$(75) & _ Chr$(5) & Chr$(6) & String(18, 0) Close #intFile End SubSample 1 The code required to mark an empty file as a Zip file
The code first checks whether the file already exists, and deletes it if it does. It then uses the Open statement to enable output to the file (thereby creating the file), and the Print statement to write the twenty-two byte signature to it.
Adding to a Zip file
To create a Zip file and add a file to it, then, requires that you initialize the Zip file as shown. You then instantiate an instance of the Shell application and use the NameSpace method of the Shell object to create a Folder object. Once you've created the Folder object, you use its CopyHere method to add the file to the Zip file. The code is presented in Sample 2.
Sub AddFilesToZip( _ ZipFile As String, _ FileToAdd As String _ ) Dim objShell As Object Dim varZipFile As Variant If Len(Dir(FileToAdd)) > 0 Then Call InitializeZipFile(ZipFile) Set objShell = _ CreateObject("Shell.Application") varZipFile = ZipFile objShell.NameSpace(varZipFile).CopyHere _ (FileToAdd) Do Until _ objShell.NameSpace(varZipFile).Items.Count _ >= 1 Call GoToSleep(100) Loop End If End SubSample 2 The code required to add a single file to a new Zip file
The reason for introducing varZipFIle into that code is that, for reasons I haven't bothered to determine, the name of the zip file must be stored in a variant in order to use it as the NameSpace. If you try to use a string variable instead, you'll end up getting an error 91 ("Object variable or With block variable not set")
You may be wondering about the reference to GoToSleep in that code. GoToSleep is a bit of code that uses the Sleep API to pause execution for the number of milliseconds specified and then continue execution, (shown in Sample 3). The reason for including it is that, depending on the size of the files being added, compressing can take an appreciable time. Realistically, it's simply a bit of a kludge to ensure that we wait until compressing is done.
Private Declare Sub Sleep Lib "kernel32" ( _ ByVal dwMilliseconds As Long _ ) Sub GoToSleep(TimeInMilliSec As Long) If TimeInMilliSec > 0 Then Call Sleep(TimeInMilliSec) End If End SubSample 3 The GoToSleep routine that pauses execution for a given number of milliseconds
Adding multiple files to the Zip file is easy too. For instance, if you wanted to add all of the files in a given folder to your Zip file, you can use the Dir function to retrieve the details of each file in a given folder. Once you know each file, you can add each one to the Zip file. Sample 4 shows the modifications required to the code presented in Sample 2 to do this. (Note that this does not add files in subfolders)
Sub AddFolderToZip( _ ZipFile As String, _ FolderToAdd As String _ ) Dim objShell As Object Dim lngFilesAdded As Long Dim strFile As String Dim varZipFile As Variant Call InitializeZipFile(ZipFile) Set objShell = _ CreateObject("Shell.Application") varZipFile = ZipFile If Right$(FolderToAdd, 1) <> "\" Then FolderToAdd = FolderToAdd & "\" End If strFile = Dir(FolderToAdd & "*.*") Do While Len(Dir(strFile)) > 0 objShell.NameSpace(varZipFile).CopyHere _ (FolderToAdd & strFile) lngFilesAdded = lngFilesAdded + 1 Do Until _ objShell.NameSpace(varZipFile).Items.Count _ >= lngFilesAdded Call GoToSleep(100) Loop strFile = Dir() Loop End SubSample 4 The code required to add all files in a folder to a new Zip file
In actual fact, it's not necessary to use a new Zip file each time. Sample 5 shows how to change Sample 2 to accommodate existing Zip files.
Sub AddFilesToZip( _ ZipFile As String, _ FileToAdd As String _ ) Dim objShell As Object Dim varZipFile As Variant If Len(Dir(FileToAdd)) > 0 Then If Len(Dir(ZipFile)) > 0 Then If FileLen(ZipFile) = 0 Then Call InitializeZipFile(ZipFile) End If Else Call InitializeZipFile(ZipFile) End If Set objShell = _ CreateObject("Shell.Application") varZipFile = ZipFile objShell.NameSpace(varZipFile).CopyHere _ (FileToAdd) Do Until _ objShell.NameSpace(varZipFile).Items.Count _ >= 1 Call GoToSleep(100) Loop End If End SubSample 5 The code required to add a single file to either a new or existing Zip file
Querying a Zip file
Since, as previously mentioned, the Zip file is treated as a Folder by Windows Shell, listing the files contained within it is straight-forward as well: the GetDetailsOf method of the Folder object will give you the information you need. I will note, however, that the information contained in MSDN under the GetDetailsOf method at http://msdn.microsoft.com/en-us/library/bb787870(VS.85).aspx appears to be incorrect. MSDN says a value of 1 for iColumn will retrieve the size of the item, a value of 2 will retrieves the type of the item and a value of 3 will retrieve the date and time that the item was last modified. I found that a value of 1 will actually retrieve the type of the item, a value of 2 will retrieve the packed size of the item (5 will retrieve the unpacked size) and a value of 7 will retrieve the last modified date/time. I also found that a value of 6 will retrieve the details of the ratio of packed to unpacked, while a value of 8 will retrieve the CRC value of the file. The code shown in Sample 6 will write the details of each file to the Immediate window.
Sub ListFilesInZip( _ ZipFile As String _ ) Dim objShell As Object Dim varFileName As Object Dim varNameOfZipFile As Variant If Len(Dir(ZipFile)) > 0 Then Set objShell = _ CreateObject("Shell.Application") varNameOfZipFile = ZipFile Debug.Print _ "Details contained in " & ZipFile & vbCrLf For Each varFileName In _ objShell.NameSpace(varNameOfZipFile).Items With objShell.NameSpace(varNameOfZipFile) Debug.Print "Name = " & _ .getdetailsof(varFileName, 0) Debug.Print "Type = " & _ .getdetailsof(varFileName, 1) Debug.Print "Last Modified = " & _ .getdetailsof(varFileName, 7) Debug.Print "Unpacked Size = " & _ .getdetailsof(varFileName, 5) Debug.Print "Packed Size = " & _ .getdetailsof(varFileName, 2) Debug.Print "Ratio = " & _ .getdetailsof(varFileName, 6) Debug.Print "CRC = " & _ .getdetailsof(varFileName, 8) Debug.Print _ vbCrLf & String(20, "-") & vbCrLf End With Next varFileName Set objShell = Nothing End If End SubSample 6 Code to list details of files contained in a Zip file
Extracting from a Zip file
Extracting the files to a known location is straight-forward as well. You can instantiate a Folder object representing the destination to which you want the files unzipped, and then use the CopyHere method to copy the files from the Zip file to that location. Sample 7 illustrates this.
Sub UnzipFilesToFolder( _ ZipFile As String, _ DestFolder As String _ ) Dim objShell As Object Dim varDestFolder As Variant Dim varZipFile As Variant If Len(Dir(DestFolder, vbDirectory)) > 0 _ And Len(Dir(ZipFile)) > 0 Then Set objShell = _ CreateObject("Shell.Application") varDestFolder = DestFolder varZipFile = ZipFile objShell.Namespace(varDestFolder).CopyHere _ objShell.Namespace(varZipFile).items Set objShell = Nothing End If End SubSample 7 Code to unzip all files in a Zip file
For more granularity, you can use the technique shown in Sample 6 to get the details of which files are contained in the Zip file, and then use the name of the specific file(s) you want unzipped with the CopyHere method. If you only want to unzip a specific file, you'd replace the line of code
objShell.Namespace(varDestFolder).CopyHere _ objShell.Namespace(varZipFile).items
objShell.Namespace(varDestFolder).CopyHere _ objShell.Namespace(varZipFile)._ items.Item(strFile)
where strFile contains the name of the file to be unzipped.
As mentioned, I've only just scratched the surface of working with the Windows Shell in this article. Yes, it's a little clunky. As far as I'm aware, there isn't a method to delete files from a Zip file (you'd need to create a new Zip file, copy the files you don't wish to delete from the existing Zip file to the new Zip file, delete the existing Zip file then rename the new Zip file). However, if what you're trying to do is add the ability to zip files from within your application (perhaps because you want to e-mail them as attachments), this technique is certainly simple to add.
Working with the Database
The database, which accompanies this article, has two forms in it.
One form (frmAddToZip) allows you to specify a file into which other files should be compressed as well as specify which file(s) should be zipped into that file. I've included code to invoke the standard Windows File Open dialog to let you select the Zip file (which, by the way, does not have to already exist), or you can simply type the file name into the text box. (You can invoke the dialog either by clicking on the button to the right of the text box, or by double-clicking on the text box itself). To select the files to add to the Zip file, though, I believe you must use the File Open dialog (which, again, you can invoke either by clicking on the button to the right of the large text box, or by double-clicking on the text box itself). It may look as though the "Files To Add" box is a list box, but it's only a text box with entries separated by a carriage return/line feed combination. (That's the reason why I'm not sure it'll work if you simply type the file names into it).
The other form (frmZipFileDetails) allows you to specify a zip file. As before, you can
either type the name of the file into the text box, or you can invoke the standard Windows File Open dialog by clicking on the button to the right of the text box, or by double clicking on the text box itself. (If you type the name in, you'll need to hit Enter once you're done in order to invoke the AfterUpdate event). Once you've provided the path to the zip file, the details of the files within that zip file will appear in the List View control below. If you also specify a folder to which to unzip the file (either by typing it into the text box, or by invoking the standard Windows Browse Folder dialog by using the button or double-clicking), you'll be given the option to unzip all of the files to that location.
Incidentally, I got lazy in the second form. I could have given the option to unzip a single file. As the comment in the sub UnzipFilesToFolder states:
' Note that if you only wanted to unzip a single file, you could change ' the line of code below to ' objShell.Namespace(varDestFolder).CopyHere _ ' objShell.Namespace(varZipFile).items(FileName) ' where FileName would be the name of the file to unzip (no path)
A couple of caveats
As I stated in the article, "The ZIP file format permits a number of different compression algorithms to be used, with the dominant method (and the one used by Windows) being the Deflate method." I've found with several of the files on which I tried using frmZipFileDetails, the technique being illustrated in frmZipFileDetails did not reveal all of the details about the files within. I'm assuming that that's because they were created using a different technique than the Deflate method. If you find that the form doesn't work for you, try a different Zip file.
Also, I may have gotten too "cute" in frmZipFileDetails when I used the List View control. I happen to like using the List View control that comes from either mscomctl.ocx or comctl32.ocx. (I also like using the Tree View control from the same file). Unfortunately, a recent security patch from Microsoft set the "kill bit flag" so that problems can occur (see http://support.microsoft.com/kb/957924). Hopefully you won't have any problems using that form!
I apologize for the fact that the sample database may leave you with more questions than it answers! There is a lot of stuff that's unrelated to the topic of Zip files in that sample: how to use the standard Windows File Open dialog, how to use the standard Windows
Browse Folder dialog, how to use the List View common control. Hopefully you can get the gist of working with Zip files (which is, after all, the point of the article), without getting distracted by the other stuff!
Any questions or comments, your best bet is to post a comment on the Database Journal
webpage. I get a lot of e-mail each day, so I do not guarantee answers to questions sent directly to me.
Download the database.