A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Wanting to grab group of files and PkZip them.



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 04:05 PM posted to microsoft.public.access
Brad@Cibc
external usenet poster
 
Posts: 3
Default Wanting to grab group of files and PkZip them.

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

  #2  
Old June 1st, 2010, 06:44 PM posted to microsoft.public.access
Albert D. Kallal
external usenet poster
 
Posts: 2,874
Default Wanting to grab group of files and PkZip them.

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.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada



  #3  
Old June 2nd, 2010, 03:49 PM posted to microsoft.public.access
Brad@Cibc
external usenet poster
 
Posts: 3
Default Wanting to grab group of files and PkZip them.

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.

"Albert D. Kallal" wrote:

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.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada



  #4  
Old June 2nd, 2010, 05:37 PM posted to microsoft.public.access
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default Wanting to grab group of files and PkZip them.

Take a look at what I've got at
http://www.databasejournal.com/featu...-MS-Access.htm

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"Brad@Cibc" wrote in message
...
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



  #5  
Old June 2nd, 2010, 06:33 PM posted to microsoft.public.access
Albert D. Kallal
external usenet poster
 
Posts: 2,874
Default Wanting to grab group of files and PkZip them.

"Brad@Cibc" wrote in message
...
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....



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada



  #6  
Old June 3rd, 2010, 02:12 PM posted to microsoft.public.access
Brad@Cibc
external usenet poster
 
Posts: 3
Default Wanting to grab group of files and PkZip them.

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!!!!

"Albert D. Kallal" wrote:

"Brad@Cibc" wrote in message
...
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....



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada



  #7  
Old June 3rd, 2010, 03:42 PM posted to microsoft.public.access
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default Wanting to grab group of files and PkZip them.

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 &




--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"Brad@Cibc" wrote in message
...
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!!!!

"Albert D. Kallal" wrote:

"Brad@Cibc" wrote in message
...
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....



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.