Excel to Zip

[Darren Hill]

Is it possible to use Excel to take all Excel files in directory, open
winzip and zip them all in individual zip files with the same name as the
Excel files?


Darren

 

[Vasant Nanavati]

Not without a great deal of work, which may be worthwhile if you have to do
this often. See this thread for some ideas:

http://google.com/groups?threadm=epqiNKMCBHA.1568@tkmsftngp07

 

[Darren Hill]

Thanks for that reference. In the thread it mentions:
============
With the shareware command line zip utility from the PKWARE web site

http://www.pkware.com/shareware/pkzipc40.html

it's easy to create a zip file using Shell:

Shell "pkzipc -add e:\zipfile @e:\list.txt"
============
Unfortunately, that's a dead link. Does anyone know where hat utility can be
found, or one like it?

 

[Paul B]

Darren, just use http://www.pkware.com/ and go from there

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[David McRitchie]

UltimateZip (.4KB download) is free and has a batch compressor.
Batch Compressor,compresses individual files to individual archives.
http://www.ultimatezip.com/

I have Ultimate Zip installed but I only use the right click menus and can't
find the actual executable at the moment you won't have any trouble
finding it if you install.

 

[Dave Peterson]

My copy of pkzipc.exe is "C:\program files\pkware\pkzipc\pkzipc.exe

This seemed to work ok for me:

Option Explicit
Sub testme01()

Application.ScreenUpdating = False

Dim myFiles() As String
Dim fCtr As Long
Dim iCtr As Long
Dim myFile As String
Dim myPath As String
Dim CurLocation As String

CurLocation = CurDir

myPath = "c:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

ChDrive myPath
ChDir myPath

If fCtr > 0 Then
For iCtr = LBound(myFiles) To UBound(myFiles)
Application.StatusBar _
= "Processing: " & myFiles(iCtr) & " at: " & Now

Shell "command.com /c " & Chr(34) _
& "c:\program files\pkware\pkzipc\pkzipc.exe" & Chr(34) _
& " -add " _
& Chr(34) & Left(myFiles(iCtr), Len(myFiles(iCtr)) - 4) & ".zip" _
& Chr(34) & " " _
& Chr(34) & myFiles(iCtr) & Chr(34), vbMaximizedFocus
Next iCtr
End If

ChDrive CurLocation
ChDir CurLocation

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub


I wanted to see if the command actually finished ok:

When I was testing, I used "command.com /k" to see the DOS window. (I think in
winNT, the equivalent is "cmd.exe /c" (or /k). (maybe winXP, too????))

In either case, you can actually just delete the "command /c " from the shell
command when you're sure it works.

And you could use vbMinimizedFocus instead of having the window maximized, too.

See Shell in VBA's help for the options.

 

[Darren Hill]

That sounds very handy, thanks.

--
Darren

UltimateZip (.4KB download) is free and has a batch compressor.
Batch Compressor,compresses individual files to individual archives.
http://www.ultimatezip.com/

I have Ultimate Zip installed but I only use the right click menus and can't
find the actual executable at the moment you won't have any trouble
finding it if you install.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thanks for that reference. In the thread it mentions:
============
With the shareware command line zip utility from the PKWARE web site

http://www.pkware.com/shareware/pkzipc40.html

it's easy to create a zip file using Shell:

Shell "pkzipc -add e:\zipfile @e:\list.txt"
============
Unfortunately, that's a dead link. Does anyone know where hat utility can be
found, or one like it?

--
Darren

to
do as
the