An interesting feature of Microsoft Excel 2016

ZIP AND UNZIP FROM MICROSOFT EXCEL USING VBA

Indeed one of the most interesting features that has me quite excited, although I have not quite figured out how exactly to use it to my best advantage is the capability built into Microsoft Excel’2016 (do not try it in any other version below 2010, however) is that you can zip and unzip any .zip extension from within Excel. Just see the pictures below; I am not saying how useful it is exactly, until I try it out in depth myself, but you are allowed to put in your comments on how best you have used it to date.

Yes, it is true that you have to use VBA to do any of this, but the basic code is given in the picture – it comes from the book I recently purchased from amazon.in called “Excel 2016 Power Programming with VBA” by Michael Alexander and Dick Kuleska (Wiley Publications). Here is the module that unzips a file to the targeted directory:

‘===============================
’Module m_UnZipAFile.bas
’===============================
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
=================================
Happy Programming !!
CA Vikram Shankar Mathur
Posted:    24-Sep-2019 | 12:55 Hours IST
Updated: 17-Dec-2024 | 18:33 Hours IST
=================================