Extraction of Zip in Excel

Standard

Below is a VB subroutineĀ to extract a zip file which can then beĀ imported into Excel spreadsheet

Sub UnZip()
    ExtractTo = "\\Destination\folder"
    ZipFile = "\\Path\To\ZipFile.zip"
 
    ' i was getting the modified date of the file so the latest zip would be grabbed
    ZipFileModDate = Format(FileDateTime(ZipFile), "yyyy-MM-dd")
 
    ' there was a date column in the Excel worksheet, the above modified date is compared to 
    ' the date in the date column
    importDate = Format(Sheets("Sheet2").Cells(18, 35), "yyyy-MM-dd")
    differenceIs = DateDiff("d", importDate, ZipFileModDate)
 
    ' if the difference was greater that 1, i.e. was from yesterday or further back
    If differenceIs > 1.1 Then
 
        ' create file system object
        Set fso = CreateObject("Scripting.FileSystemObject")
        If Not fso.FolderExists(ExtractTo) Then
               ' if folder to extract to doesnt exist, create it
               fso.CreateFolder (ExtractTo)
        End If
 
        ' extract and copy files to destination folder
        Set objShell = CreateObject("Shell.Application")
        Set FilesInZip = objShell.Namespace(ZipFile).items 
        objShell.Namespace(ExtractTo).CopyHere (FilesInZip)
 
        ' clear objects 
        Set fso = Nothing 
        Set objShell = Nothing 
    End If 
End Sub