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

0
Utf
6/1/2010 3:05:01 PM
access 16762 articles. 2 followers. Follow

6 Replies
842 Views

Similar Articles

[PageSpeed] 34

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
pleaseNOOSpamKallal@msn.com
 

0
Albert
6/1/2010 5:44:15 PM
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
> pleaseNOOSpamKallal@msn.com
>  
> 
0
Utf
6/2/2010 2:49:01 PM
Take a look at what I've got at 
http://www.databasejournal.com/features/msaccess/article.php/3835766/Working-with-Compressed-zipped-folders-in-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" <BradCibc@discussions.microsoft.com> wrote in message 
news:A7DEC820-2746-40AE-AB7A-E03B2F2B7F10@microsoft.com...
>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
> 


0
Douglas
6/2/2010 4:37:37 PM
"Brad@Cibc" <BradCibc@discussions.microsoft.com> wrote in message 
news:192529B8-D0BC-404A-B9AA-F7E3CD00E7A4@microsoft.com...
> 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
pleaseNOOSpamKallal@msn.com
 

0
Albert
6/2/2010 5:33:44 PM
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" <BradCibc@discussions.microsoft.com> wrote in message 
> news:192529B8-D0BC-404A-B9AA-F7E3CD00E7A4@microsoft.com...
> > 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
> pleaseNOOSpamKallal@msn.com
>  
> 
0
Utf
6/3/2010 1:12:23 PM
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" <BradCibc@discussions.microsoft.com> wrote in message 
news:CC2AD1FE-ADA2-41D0-B6F3-B14C1D4D7C55@microsoft.com...
> 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" <BradCibc@discussions.microsoft.com> wrote in message
>> news:192529B8-D0BC-404A-B9AA-F7E3CD00E7A4@microsoft.com...
>> > 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
>> pleaseNOOSpamKallal@msn.com
>>
>> 


0
Douglas
6/3/2010 2:42:24 PM
Reply:

Similar Artilces:

How to control the .edb .stm files to a certain size
How to control the .edb & .stm files to a certain size. It seems to growth each day... Would exchange stop function when they reach 16GB? Is there any process need to run or any option need to apply to clear the file size? thanks for advice. Hi, "lamlam" <lamlam@discussions.microsoft.com> schrieb im Newsbeitrag news:940DB421-B14C-42E1-BF06-D3FA26969697@microsoft.com... > How to control the .edb & .stm files to a certain size. It seems to growth > each day... you could define limits for the mailboxes via systemmanager on the store. > Would exchange st...

QIF file
Hello, I can't find this information anywhere else. What is the difference between a QIF file and a QDF file. QIF - Quicken Information exchange File - I think QDF - Quicken Data File. Why won't money read the QDF file but not a QIF file? In microsoft.public.money, Won Lee wrote: > >I can't find this information anywhere else. >What is the difference between a QIF file and a QDF file. > >QIF - Quicken Information exchange File - I think QIF is a simple format for downloading, exporting, and importing data. >QDF - Quicken Data File. > QDF would be a Qu...

Outlook 2003 pst file not readable by Outlook 2000!
Hello Don't know if I was doing something wrong but I copied a pst file from Outlook 2003 to a machine running Outlook 2000 and it complained that data file was not compatible or something. Is this normal for newer version pst files to not be compatible with earlier version Outlook? Angus Of course. That's been exhaustively documented. If you want to create a PST file that will be backwards compatible in Outlook 2003, you must do so expressly. File > New > Outlook Data File > Outlook 97-2002 -- Russ Valentine [MVP-Outlook] "Angus Comber" <nospam@gmail.com&...

group mailing
Is there any way that I can conceal the individual addresses when I do a group mailing? If this isn't possible in group mailing, is there another way to do it with MS Mail? All of this applies to OE, WinMail and WLMail. To use the BCC (Blind Carbon Copy) feature, in a new message window, either click View | All Headers and type in the addresses separated by a; Or: Click the To button. You will see your list of contacts and the choice of putting them in the To, CC, or BCC field. Click on an address, then click BCC to add it. Repeat for all the contacts you want. (Note: Mo...

Writing to a file
I am using the WriteString method of CStdioFile which throws an exception saying that the disk is full. There is plenty of space on my hard disk. I have opened the file in read/write mode and have performed a few ReadString's before executing the WriteString. Can anybody explain this behavior. Thanks Moiz >-----Original Message----- > >I am using the WriteString method of CStdioFile which >throws an exception saying that the disk is full. There >is plenty of space on my hard disk. I have opened the >file in read/write mode and have performed a few >ReadSt...

want questions
Hello Everyone, I want some set of questions on Word-2007 to check competency level of the user. Please help. -- Ananta Gupta Ananta, please refer to my other 2 postings: just check out the WORD part. If my comments have helped please hit Yes. Thanks. "Ananta Gupta" wrote: > Hello Everyone, > > I want some set of questions on Word-2007 to check competency level of the > user. Please help. > > -- > Ananta Gupta "trip_to_tokyo" wrote: > Ananta, please refer to my other 2 postings: just check out the WOR...

I want more!
I need to extend a form that aleady has some 100 lines. For instance, one column has the date, but only until tomorroq. How do I create more lines based on the same formula? Thank you JB I usually select the last row in the range and copy it down a few rows. If you're using xl2003, you may want to look at Data|List. And if you're using xl2007, this feature has transformed into Tables. (Data tab on the ribbon|Data tools Group|What if Analysis|Data table) JB wrote: > > I need to extend a form that aleady has some 100 lines. For instance, one > column has the da...

I don't want a page of the same label, I want one of each...
I can't find where to tell Pubisher that I want one of each label and not a page of each label. I'm using Publisher 2003 (Office 2003) I see a post that says to enter "<<Next Record>>" but Publisher doesn't see this as a field, just as text. This can't be that hard, but I can't figure it out... Any help would be appreciated. Kelvin I see that it prints correctly, but the print preview displayes it wrong.... I think I have it figured out... "Kelvin" <someone@domain.com> wrote in message news:OSpLwThUIHA.5508@TK2MSFTNGP04.phx....

Best Windows 7 group?
I've used this forum several times to solve XP probs and found the people here extraordinarily helpful in ironing out my special (often self-caused) bugs. Now I've moved to Windows 7 Ultimate and am wondering if anyone can suggest a similar Google-accessible newsgroup which could be similarly useful. I've tried the moderated group just called 'windows 7' but get the idea that no-one there really knows the system in-depth the way people here understand XP. Best wishes "anthony" <anthonyjhcnospam@netscape.net> wrote in message news:16a9cd19-6a6b-...

Text want wrap in text box
I can't get the text to wrap in a text box, word wrap is set to true, I put some text boxes on a sheet today and the text will not wrap, I did this yesterday and it worked fine, I can copy the ones from yesterday and paste them on a new sheet and they work, any ideas? I have rebooted but this did not help. Using Excel 2000 (9.0.6926 SP3) Thanks Paul B If you're referring to a Control Toolbat text box make sure that the MultiLine property is True. -- Jim Rech Excel MVP "Paul B" <pbridgesnews@uga.edu> wrote in message news:%23ovLUkuLEHA.2456@TK2MSFTNGP12.phx.gbl......

Partially Corrupt MNY file
I have an MNY file that has two corrupt accounts in it. The MNY file has 5+ years worth of transactions and 20+ E/A pay transactions associated with one of the corrupt accounts. The corruption manifests itself as missing transactions in the downloaded transactions (i.e. the account shows 30 transactions to review, but none actually appear in the account register). Support has been less than helpful (I think they just use Cal's suggestions), according to them my only option is to either create a new MNY file, or cancel all E/A pays, delete the account & recreate it. The existi...

Saving Large Files
I am saving a large Publisher file and recieve the error "Cannot save file - delete some objects", Is there a file size limitation for a publisher file?? Publisher 2000 has a 2 gigabyte limit. Do this, view, pictures, turn off detailed display. Delete some of your temp items. Type %temp% in the address bar in Windows Explorer. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Large Publisher Files" <Large Publisher Files@discussions.microsoft.com> wrote in message news:F7C65EA5-3D53-46CB-9C4A-A2DB8333FB91...

how can make a mail copy to a group
i using win 2003 , exchange 2003 if have a user mail need forward ( make a copy ) to many users how can i do this ? i just know how to make a copy to one copy thx On Sat, 12 Mar 2005 01:03:12 +0800, "tequila" <tequila529@gmail.com> wrote: >i using win 2003 , exchange 2003 >if have a user mail need forward ( make a copy ) to many users >how can i do this ? > >i just know how to make a copy to one copy >thx > > Have you looked at creating a Dirstribution Group within Active Directory Users & Computers? You can mail enable it and make a number of...

PST file
i pasted my old pst file from my other computer into the same folder on my new computer. Now it says it doesnt recognise it as a personal folders file and wont even open outlook up. I dont know what to do. Can anyone help please? - Emma What versions of Outlook are we talking about here? -- Roady [MVP] www.howto-outlook.com Tips of the month: -Setting Permissions on a Mailbox -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Emma" <emma_gray@excite.com> wrote in message news:1315f01c443b0$412315e0$a301280a@phx.gbl... >i pasted my old pst file from my ...

Want to Add more Sub description Field
How I can add more sub description field like 1,2,3,4,5 regards Saleem Suri Salaam Saleem, You have only limit up to 3 fields in RMS 1,2,3 sub-description also you can use the extended description and there is main description for the item. so Altogether you have upto 5 description limit and I think that is enough. Let me know if this won't fulfill your requirements else use the Item Notes for more description. Rate if like Regards Akber "Saleem Suri" wrote: > How I can add more sub description field like 1,2,3,4,5 > > regards > Saleem Suri Salam Akbar ...

Rebuilding EDB From Log Files
My Apologies If This Is A Repost, Didn't See The Message Go Thru: Has a utility been made available to update 7.3704.5 log files to 7.3704.8? I'm try to rebuilding my edb files from the log files and its throwing the JET_errInvalidCreateDbVersion error because the first couple of log file are Exchange 2003 RTM and the the remaining logs are Exchange 2003 SP1. Running eseutil /ml on the logs dumps out: Log file: C:\Program Files\Exchsrvr\MDBDATA\E0000001.log lGeneration: 1 (0x1) Checkpoint: NOT AVAILABLE creation time: 12/22/2004 05:06:22 ...

how can I open MS SQLsharepoint database files
how can I open MS SQLsharepoint database files MSDE 2000 format I have a copy of the whole SQL folder from c:\program files. From a SBS 2003 instalation that crashed and oem does not allow me to do a repair and just would lieke to see what the data contains, even if I can get to to an excel or Access database to print out would help. Maybe... (haven't tried it myself) Using SharePoint Database Explorer on SBS http://blogs.technet.com/sbs/archive/2007/01/05/using-sharepoint-database-explorer-on-sbs.aspx -- Merv Porter [SBS-MVP] ============================ ...

I want to add a sound event when the cursor bumps left margin
I want to know (via an event sound) that my cursor movement has bumped the left edge of the spreadsheet. I am entering thousand of rows of data, and it would help if there was a event sound when I returned the cursor to the left edge. I have this feature in 123, and find it helpfull. Put this in the sheet module: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static ColOld As Integer If Target.Column = 1 And ColOld > 1 Then Beep ColOld = Target.Column End Sub -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "m...

Collapsing "groups"
Hi Guys I am new to Entourage, previously using windows os with Outlook 2007. I was wondering if there is an easy way to collapse the "groups" when you arrange messages by dates. That is instead of seeing every message that appeared yesterday, I just see the headings Yesterday, Monday, etc and then if I want to drill down into the detail I just click the heading. If anyone could offer assisatnce I would really appreciate it. Regards Ted Hi Ted, just click on View>Collapse All. On 16/12/09 4:51, in article C74EA46D.99E%ted@onepointfinance.com.au, &q...

Excel wants to create a page break in every cell!
After saving revisions in an Excel worksheet, I get a popup message that states "Margins do not fit page size". After clicking "ok" (because this is my only choice) a page break is automatically created in ever cell. At times(depending on the sizeof the work sheet) it can be over 100 pages with a page size 14% or smaller. Why does this happen, and how can I avoid it? I saw this suggestion in a different forum. Try closing excel. Clean up your windows temp folder Windows start button|Run type: %temp% and hit enter Clean all those files that you ca...

Multiple Group in Reporting Svs
Hi, I would like to design my Report in Matrix format where I have "rows" set to Student RollNo ... and "columns" group is set to Student Subjects ( these subjects are fetched from a table .... it could be 3 or 4 per student). Now, I also need to have another group to be attached to the existing "coulumns" which will display Student Progress (again... the Progress fields are getting fetched from a table .. there are 3 fields) So, over all my Report should be something like this : -------------------------------------------------------------------...

WANTED
WTB: I BUY SOFTWARE - CHECK AROUND - YOU PROBABLY HAVE SOME OF THE BELOW TO SELL TO ME. SEE A LIST OF SOME OF THE SOFTWARE THAT I AM BUYING! I AM BUYING ALL OF THE BELOW. THE MORE QUANTITY THE BETTER! I am looking to buy large quantities of Windows 7 Professional Upgrade Dell DVDs with unactivated COAs included in a SEALED package I am looking to buy quantities of SYMANTEC NORTON ANTI VIRUS, NORTON INTERNET SECURITY AND 360 OEM AND RETAIL BOXES I will buy 2010, 2009, 2008, 2007 and 2006. If you have good quantities setting around and no longer need send me detai...

PC .doc file crashes word (Snow leopard)
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel My system is fully up to date - I install all auto-updates. <br><br>I have had no problem with any .doc file from my PC, and I have imported many. <br><br>An A5 portrait flyer .doc with pictures from a PC (received as an e-mail attachment) opens but almost immediately crashes mac word while trying to convert it to 2 A5 &quot;pages&quot; on a 2 column A4 landscape page for lazer print to be guillotined (print 2 pages on one sheet does not work with the snow leopard driver for my HP...

Check boxes
Hi---I am setting up a form with various check boxes. I want to set up a range of check boxes so if any of these boxes are checked, then one corresponding check box if also checked. On the flip side, if none in the range of boxes are checked, then the one corresponding box is also not checked. I hope this can be done with a formula or conditional formatting, instead of a macro....if it takes a macro, whoever resonds to this question, please explain in easy terms---I don't have a lot o fmacro experience. Thanks!!! A boolean checkbox reflects the TRUE / FALSE value of the cell it ...

Batch File in a SQL job
Does anyone know how to set up a batch file in a SQL job to log in to GP and have a macro run overnight ? We can get the macro to work outside the SQL job, but can not get the macro to run in the Enterprise Manager job. I've never tried using a SQL Job for that but Windows Scheduler works great. Can you use that instead of SQL to schedule the .bat file? MichaelJ has one good approach. I am wondering exactly what you are trying to accomplish as there are a couple of approaches. Why SQL? If MichaelJs approach does not do what you need. please give us some more details. I have...