Looking to Zip files from within Access

[Kat]

Hi,

I first would like to say I'm mostly a self taught access programmer with a
bit of excel and word thrown in for good measure. Therefore please excuse me
if I don't use standard terminology as I may have never learned it. My
latest employment has really streached my skills and I have learned a lot
from these boards both about specific things I needed at work and just
general good practice.

That said, I've been faced with a bit of a challenge. I need to automate
and send out several (talking 50-100) excel files via e-mail. I've worked on
the automation code and have that working fine. I have a MAPI set up that we
use to send e-mails that works great.

The problem is that because the excel files contain protection they can be
rather large with data in them. The template file is just short of 1Mb and
with data the test file I am using is 3.8Mb. I have tried trimming down the
files by deleting unnecessary cells in the template but this has only reduced
it to the size above because of some list boxes populated with data in hidden
cells.

I am worried that these files will get bounced by receiving e-mail clients
for being too large or the receiver not having enough mail box space.
Normally, if there weren't as many files, I would simply zip the files (using
the windows xp built in zip function) manually. This time we have to send
files to the entire organization so doing it manually would take me most of
the day.

Therefore my question is, is it possible to automate ziping of files from
within access VBA? Does anyone possibly have any examples of code on how to
do this? I assume it would have to be done with an API or shell call but the
aim is for the code to be fully automated not requiring any input from the
user (me).

Conversly if anyone knows this is definately not possible I'd appreciate
hearing that too so I can move on and just work on getting the zip files
ready to receive their information on the day this all needs to be run. (Yes
all the files need to go out on the same day, which is part of the problem)

I currently run Access 2003 on windows XP professional. Most of our
databases are saved as windows 2k databases.

Thanks again for any help anyone can give.

Kat

 

[Albert D. Kallal]

I don't have a lot of documentation as of yet, but I have a WinZip example
in access here:

http://www.members.shaw.ca/AlbertKallal/zip/index.htm

 

[Kat]

I've downloaded and had a look at the code in your example and I must admit a
lot of it goes well over my head.

Just one question, I don't see any explicit references to WinZip, but I
assume from what you have said, that it requires the commercial program
WinZip to work? or will it work with the built in zip that windows xp has? Or
am I being thick and they are the same it just doesn't say so?

I've not tried it out because our work computers don't have WinZip (or at
least the program I remember) and snooping round the code I realized I was
well out of my depth for exactly what is happening where. I get a bit lost
when it starts getting into class modules and types. I'm prolly missing out
on "a good thing" but maybe I'll get into them at some point =).

Kat

 

[Albert D. Kallal]

Just one question, I don't see any explicit references to WinZip, but I

assume from what you have said, that it requires the commercial program
WinZip to work?

No, it does not require winzip to be installed on the computer.

or will it work with the built in zip that windows xp has?

No, it does not require the windows winzip libary.

Or
am I being thick and they are the same it just doesn't say so?

No, the download sample *includes* a copy of zipping code
that is availing for free. This means that you must have the
two files

zip32.dll and UnZip32.dll placed in the same directory as yoru mdb(or mde)
application.

I've not tried it out because our work computers don't have WinZip

With my example, you don't need to install or purchase any additional
software. You just need to ensure the 2 above .dll files are in the same dir
as the applicaton.

 

[Tony Toews [MVP]]

Just to add to Albert's comments. That code and the dlls have worked
quite well for me. Although I do realize VBA code can be tough to
get going on.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Kat]

Thanks Albert. This is exactly what I needed then. I really appreciate the
help. Sorry for being a bit thick, as I said the code was quite a bit more
complex than I am used to.

Kat

 

[Kat]

Hi Albert,

First I want to thank you for writing this wonderful bit of code. It does
exactly what I need it to do and saves me litterally hours of work in the
coming weeks. I've added it to a test file creation and it works great.

I tried to have a bit deeper look at the code and figure this out for myself
but I'm afraid its still all just a bit too complex for me (although I did
figure some of it out!).

My question is, is there any way to stop the zip file from containing all
the folders that the excel file I am zipping is in? For example, the file to
be zipped is in directory X:\Myfirstfolder\MysecondFolder\myfile.xls (network
location) and it shows up in the zip file as
Myfirstfolder\MysecondFolder\myfile.xls. It doesn't pose too much of a
problem but I would prefer for the zip file to just contain myfile.xls
without all the folders. I figured a work around would be to save it to a
root directory as well as the network location and delete the root copy once
the file is zipped. These are going out to various places and they won't be
interested in our folder structure.

Thanks again for any help you can give.

Kat