Excel to Zip

  • Thread starter Thread starter Darren Hill
  • Start date Start date
D

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
 
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, 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 **
 
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.
 
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.
 
That sounds very handy, thanks.

--
Darren
David McRitchie said:
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.
Click to expand...
 
Back
Top