Friday, May 31, 2013

Compressing Files for Archiving and Emailing

The Good News

I was looking for a way to compress (not ZIP as in 7zip or WinZIP) Excel files for emailing and archiving.  After a an hour or so of reading posts where this question was asked often and never answered for VBA, and often answered for those who can reliably say their users will have WinZIP (a fine product in my book...this problem is not a slight to that team)  available.

Google brought me to Ron de Bruin's site, specifically this link:

I was so excited I immediately posted "Haven't tested this yet.  Will commence now!  ...."

Twenty minutes later I started writing ...

The Review

OK.  I tested it.  Here's what I found
  • Works as advertised!
  • Code is well structured and is well documented.
  • There is only one black box, meaning code I do not yet understand but it works every time.  This is an opportunity for me to learn from someone who did it right rather than my usual hack 'til it quits method.
  • Any changes I need to make to fit my needs will be easy to make.
  • My only "complaint" is such a small nit if Ron de Bruin were to see this "complaint" without me first saying it is tiny, minuscule, and barely worth a thought he might block me from his site, and maybe call Interpol to keep me out of Netherlands. that I've wasted time and energy explaining how small it is, it using the word "zip".  I prefer "compress" because 
    • "Compressed" is  the word used by the people who developed the compressed folder structure.
    • "ZIP" is something I think of when referring to the products like WinZIP, and PKZIP before that.  Those are much richer products.
    • "Zip" is also a new-to-me method of handling files where one can add the "zip" extension, insert a file within the ZIPped file thus modifying the XML (this is my understanding of what happens, then removing the "zip" extension to reveal a modified filed.
      LAST NOTE ON HOW TINY THIS COMPLAINT IS:  I believe all of the code Ron has provided was written before the zip/XML method became commercial.

What I didn't understand

Here's a subroutine used by the main Subs that is beyond my knowledge at this point

Sub NewZip(sPath)
'Create empty Zip File
'Changed by keepITcool Dec-12-2005
    If Len(Dir(sPath)) > 0 Then
        Kill sPath
    End If
    Open sPath For Output As #1
    Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1
End Sub

I haven't dealt with number file streams in decades and have no idea what " Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)" is doing.