How can I GZip compress a file from Excel VBA using code in an .xla file only?

Question

I need to be able to GZip compress a file in an Excel VBA function. Specifically I need to be able to use the 'deflate' algorithm.

Is there a way to do this without having to exec a command line application? With no dependency on external tools the code will be more robust.

Ideally the code would make use of pre-installed VBA or COM library functions - I don't want to have to implement this logic myself or install DLLs etc.

If possible, I want installation of the function to be as simple as adding a .xla to the available Excel Add-Ins. No DLLs, EXEs, registry entries etc. required.

Edit Can I make use of the .NET GZipStream to do this?

Answer 1

If you want to implement the algorithm in VBA, you would need to (in VBA) save the spreadsheet and then use VB's I/O functions to open the file, deflate it, and save it again. For all intents and purposes it's identical to writing an ordinary VB application that works on a file. You might need to put the VBA macro in a separate workbook to avoid "file in use" types of errors, but if you reopen the file as read-only and save it with a different filename you should be OK keeping everything in one workbook.

But I'm almost certain that shelling out to gzip from within the VBA would be functionally identical and infinitely easier.

EDIT: Some code. It didn't fail when I ran it, so it's OK to keep everything in the same workbook.

Sub main()
    ActiveWorkbook.Save
    Open "macrotest.xls" For Binary Access Read As #1
    Open "newfile.zip" For Binary Access Write As #2
        'do your stuff here
    Close #2
    Close #1
End Sub

Answer 2

VBA (which is really a dialect of VB6) is slow for these kind of applications. I remember I once implemented Shannon-Fano algorithm on VB6 and on C, the C version was about 10 times faster, even after being turned into a DLLMain and called from there rather than on a command-line executable.

There are lots of COM DLLs that provide compression services, both open source and shareware, and some of them implement GZIP's deflate algorithm. It'd be really simple to just call one function from such a DLL from your VBA code to do the compression on your behalf.

I understand your being reluctant on using something external to your application, though in this case you might have to apply an exception for performance's sake.

In an effort to completely spoil your fun, examine file ZIPFLDR.DLL on windows\system32. you may also like to take a look at these links:

• This has an example of how to do what you want (zipping using windows built-in ZIP capabilities) from VB.NET, it shouldn't be much different from VBA or VB6: Transparent ZIP with DLL call
• This one has a sample application on VB6 using windows built-in capabilities to zip (in ZIP rather than GZIP format, of course): Using Windows XP "Compressed Folder" shell extension to work with .zip files

Found both thru googling, you should be able to find more/better examples.

Answer 3

It seems that you want to open a bottle of wine but you definitly refuse to use a bottle-opener. As long as there is no VBA function allowing the GZipping of a file, you will not be able to do the job without some external ressource such as a dll or exe file.

Answer 4

OK, I think I have an answer for you.

zlib is a library written by the guy that wrote the deflate algorithm you don't want to implement. There is a win32 DLL available. Here's the FAQ regarding using it from Windows:

http://www.zlib.net/DLL_FAQ.txt

Check out question 7. The authors don't seem too keen on Windows users, and don't seem at all keen on VB users, but as long as they're kind enough to provide the library we can do the rest.

If this is enough to help you, then great. If you want help with calling the C library from VBA add a comment and we'll figure it out. I haven't done any VB-to-C calls in years--it sounds like fun.

Answer 5

If somebody wanted to compress files without relying on 3rd-party software they would generally implement it as a COM object/DLL so it would be available to more than just Excel. If somebody wanted to incorporate zip functionality into Excel they would use 3rd-party tools so they wouldn't have to re-implement the algorithm. So you're swimming against the tide. However...

http://www.cpearson.com/excel/SaveCopyAndZip.htm

There are two versions. The COM Add-in version "...allows you to zip any workbook that has been saved to disk (but it may be in an unsaved state)." It relies on a Moonlight Software component but all the components and set-up are contained in the installer. It's not quite public domain but the license is less restrictive than the GPL. The end result is an Excel add-in (that uses a 3rd-party component).

But if you really, truly don't want any dependencies on external tools you're either going to have to implement the compression algorithm yourself or wait until Microsoft builds that functionality into Windows and exposes it through Excel.

I hope this helps.