ExcelBanter - Excel to Zip

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Excel to Zip (https://www.excelbanter.com/excel-programming/283402-excel-zip.html)

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

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=epq...%40tkmsftngp07

--

Vasant

"Darren Hill" wrote in message
...
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

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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=epq...%40tkmsftngp07

--

Vasant

"Darren Hill" wrote in message
...
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

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 **
"Darren Hill" wrote in message
...
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

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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=epq...%40tkmsftngp07

--

Vasant

"Darren Hill" wrote in message
...
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

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

"Darren Hill" wrote in message ...
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

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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=epq...%40tkmsftngp07

--

Vasant

"Darren Hill" wrote in message
...
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

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 wrote:

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

--

Dave Peterson

That sounds very handy, thanks.

--
Darren
"David McRitchie" wrote in message
...
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

"Darren Hill" wrote in message
...
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

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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=epq...%40tkmsftngp07

--

Vasant

"Darren Hill" wrote in message
...
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