Home > Excel, VBA > Shrinking Excel 2010/2007 Files by about 25%

Shrinking Excel 2010/2007 Files by about 25%

September 9th, 2011 admin

We know the compression technology Excel uses to create and save the files is not optimum.
So after reading a post on BaconBits about it, I thought I would write a macro who creates (small) copies of selected excel files.
The files I produced were all around 25% smaller than the originals.
Worth the effort I would say….

Bare in mind if you save the created files they will grow again….

you can download a sample workbook here Shrink Excel Files

Sub ShrinkExcelFiles()

    Dim Fname
    Dim i As Long
    Dim sFileFolder As String

    Fname = Application.GetOpenFilename(filefilter:="Excel (*.xls*), *.xls*", _
            MultiSelect:=True, Title:="Select the Excel files you want to shrink")  

    If IsArray(Fname) = False Then
    Else

       For i = LBound(Fname) To UBound(Fname)

           sUnzipFolder = Left(Fname(i), InStrRev(Fname(i), "\")) & "unzip\"
           ShrinkXlsX Fname(i), sUnzipFolder

       Next i

    End If

End Sub
Sub ShrinkXlsX(sFileName, sTempFolder)

    Dim objApp As Object
    Dim vFileName As Variant
    Dim sFileExtension As String
    Dim i As Long

    sFileExtension = Right(sFileName, Len(sFileName) - InStrRev(sFileName, "."))

    Name sFileName As sFileName & ".zip"

    CreateFolder sTempFolder

    Set oApp = CreateObject("Shell.Application")

    For Each ItemInZip In oApp.Namespace(sFileName & ".zip").items

        oApp.Namespace(sTempFolder).CopyHere (ItemInZip)

    Next

    Open sFileName & "_Small.zip" For Output As #1
    Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1

    i = 1

    For Each ItemInFolder In oApp.Namespace(sTempFolder).items

        oApp.Namespace(sFileName & "_Small.zip").CopyHere (ItemInFolder)

        Do Until oApp.Namespace(sFileName & "_Small.zip").items.Count = i

            Application.Wait (Now + TimeValue("0:00:01"))

        Loop

        i = i + 1

    Next

    Name sFileName & "_Small.zip" As sFileName & "_Small." & sFileExtension
    Name sFileName & ".zip" As sFileName

    DeleteFolder sTempFolder

End Sub
Sub DeleteFolder(MyPath)

    Dim FSO As Object

    Set FSO = CreateObject("scripting.filesystemobject")

    If Right(MyPath, 1) = "\" Then MyPath = Left(MyPath, Len(MyPath) - 1)

    If FSO.FolderExists(MyPath) = False Then Exit Sub

    FSO.DeleteFolder MyPath

End Sub

Sub CreateFolder(MyPath)

    Dim FSO As Object

    Set FSO = CreateObject("scripting.filesystemobject")

    If Right(MyPath, 1) = "\" Then MyPath = Left(MyPath, Len(MyPath) - 1)

    If FSO.FolderExists(MyPath) = True Then DeleteFolder MyPath

    FSO.CreateFolder MyPath

End Sub
Categories: Excel, VBA Tags: ,
Comments are closed.