Programmatically Unzipping an Excel Container

The unzip procedure is little more than a series of simple steps that duplicate the manual act of copying files out of an Excel container and saving them into a destination folder.

There are probably dozens of different methods and utilities that can be used to programmatically zip and unzip a compressed file. The procedures demonstrated here leverage the built-in file compression functionality within Windows XP. If you do not have Windows XP, you can use any one of dozens of compression software packages that provide command line and shell utilities for managing zip files. To find one, simply enter "Zip Command Line" in your favorite search engine.

First, load the name of your target Excel file to the TargetFile variable. The NewFileName is defined as the TargetFile string concatenated with the .zip extension. Next, use these variables in a FileCopy statement, essentially copying the target Excel file and saving it with a .zip file extension. This converts the target Excel file to a temporary .zip file while keeping the target file intact. Then create a destination folder and copy each of the XML parts located in the temporary .zip file into the destination folder. Once all XML parts have been copied, delete the temporary .zip file:

Sub UnzipPackage()

Dim o As Object

Dim TargetFile, NewFileName, DestinationFolder, ofile

'Define the source file path and the path for the new file TargetFile = ThisWorkbook.Path & "\SalesByPeriod.xlsx" NewFileName = TargetFile & ".zip"

'Create the temp zip File

FileCopy TargetFile, NewFileName

'Define a destination folder path and Make the destination folder DestinationFolder = "C:\MyUnzipped" On Error Resume Next MkDir (DestinationFolder)

'Copy each file to the destination folder

Set o = CreateObject("Shell.Application")

For Each ofile In o.Namespace(NewFileName).items o.Namespace(DestinationFolder).CopyHere (ofile) Next ofile

'Clean up

Kill NewFileName Set o = Nothing

End Sub

0 0

Post a comment