Wanting to grab group of files and PkZip them.

[Brad@Cibc]

I have an excel file that has two columns, Code and Filename.
i.e.
CODE Filename
Abc 123456.txt
Abc 123457.txt
Def 123458.txt
Def 123459.txt
Etc…
I would like to be able to select all files with a specific Code and have
them zipped into a file that is named the same as the Code. i.e. Abc.zip
would contain 123456.txt & 123457.txt AND Def.zip would contain 123458.txt &
123459.txt
The file names are actually quite random but they are associated with
specific codes according to this Excel file.
I am creating an Access form that will allow users to browse to the Excel
file and would like to be able to code this form to perform the above
operation automatically. Is this possible in VBA? We are using Access 2003 &
Pkzip
Any ideas?
Regards
Brad

 

[Albert D. Kallal]

I have some Access code here that should help:

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


You don't need pkzip installed for it to run, but do have the place the
included .dll's in the same dir.

 

[Brad@Cibc]

Thanks so much Albert!
However, as i understand it, your code doesn't allow selection of multiple
files.
and I am having a heck of a time figuring out the syntax for the Shell
command to run this command line.
pkzipc -add test.zip *.doc
Could you please advise?
Thanks again.

 

[Douglas J. Steele]

Take a look at what I've got at
http://www.databasejournal.com/feat...th-Compressed-zipped-folders-in-MS-Access.htm

 

[Albert D. Kallal]

Thanks so much Albert!
However, as i understand it, your code doesn't allow selection of multiple
files.

Well, I just don't have a UI setup, but can pass it wild cards and it will
work fine. So, you not limited to just one file.

It really comes down to how the selection of files is going to occur
here....

 

[Brad@Cibc]

This may be a double Post, please forgive me, the system seemed to fail on
the first attempt.
First of all let me thank yourself and Doug Steel for the extensive code. It
may be in fact more than I need. After playing with some code, as I see it, I
may be importing the Excel file into a table and then creating & cycling thru
a recordset to look for a specific Code and then select the Filename to Add
to a specific Zip file, then go to the next record and continue...
So my question is; "How do I execute the Pkzip command line from within
VBA?" The is is what the Pkzip Manual says has to be executed.
pkzipc -add ABC.zip filenam.doc
But i don't understand how to do that from VBA.
Am i making any sense?
Thanks Guys!!!!

 

[Douglas J. Steele]

Try:

Shell "pkzipc -add ABC.zip filenam.doc", vbHide

If you're trying to include full paths to file names, remember that if there
are spaces in the path, you have to enclose the name in quotes:

Shell "pkzipc -add C:\Archive\ABC.zip ""C:\Folder With Space\filenam.doc""",
vbHide

If you're trying to do this with variables, assume your archive name
(ABC.zip) is stored in variable strArchive, and you're trying to add strFile
(filenam.doc)

Shell "pkzipc -add """ & strArchive & """ """ & strFile & """", vbHide

That's three double quotes in a row before & strArchive, three double quotes
in a row followed by a space then three more double quotes in a row after
strArchive & four double quotes in a row after strFile &