winzip in macro

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Help, I have written a macro that performs calculations in
a spreadsheet and e-mails it to the relevant people. The
problem I have is that the file is large and would benefit
from compression, is there a way of writing this in to the
Macro.

Thanks
Michael
 
Hi Michael,

on the winzip-website you should find a download called something like
"command line utility" which zips files with lots of parameters from the
command line. You can include this compose this command in the excel-makro
with the "Shell" command, look vba-help for details on shell.

regards

arno
 
Michael,

Here is something Ron de Bruin wrote on this subject

On the zipping part, you may want to check WinZip's web site. A few months
ago they introduced an addin to Winzip called the command line support addon
which lets you enter command switches with the Basic Winzip command. you
could then use some code like this:

Sub Zip_File()
Dim Zip_Path As String, Command_Text As String, My_Prog
Try_Drive
Zip_Path = File_Sh.[AH13]
' Delete the old zip file if it exists to avoid adding this file
Command_Text = Zip_Path & Path_Sep & "wzzip -yp spc.zip spc.xls"
My_Prog = Shell(Command_Text, vbMaximizedFocus)
End Sub

The one "gotcha" I never got past was that unless the user is using Outlook,
the only file I could zip and mail was the open workbook using the
File.SendMail command. One of these days I'm going to write a macro to use
outlook but just haven't yet gottn to it.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Michael said:
Help, I have written a macro that performs calculations in
a spreadsheet and e-mails it to the relevant people. The
problem I have is that the file is large and would benefit
from compression, is there a way of writing this in to the
Macro.

Thanks
Michael
Click to expand...


There are a number of libraries available for this ranging
from the free infozip libraries (a little tricky to use from VBA
I suspect) to the commercial libraries marketted by XCEED

http://www.vbaccelerator.com/codelib/zip/zipvb.htm
www.xceedsoft.com/products/ZipCompL/

You may simply be able to use the old DOS Pkzip program
with the shell command

Keith
 
Hi Bob/Michael

See this example

http://www.rondebruin.nl/sendmail.htm#Zip

My Outlook Add-in have this option also
http://www.rondebruin.nl/sendmail-addin.htm

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Bob Phillips said:
Michael,

Here is something Ron de Bruin wrote on this subject

On the zipping part, you may want to check WinZip's web site. A few months
ago they introduced an addin to Winzip called the command line support addon
which lets you enter command switches with the Basic Winzip command. you
could then use some code like this:

Sub Zip_File()
Dim Zip_Path As String, Command_Text As String, My_Prog
Try_Drive
Zip_Path = File_Sh.[AH13]
' Delete the old zip file if it exists to avoid adding this file
Command_Text = Zip_Path & Path_Sep & "wzzip -yp spc.zip spc.xls"
My_Prog = Shell(Command_Text, vbMaximizedFocus)
End Sub

The one "gotcha" I never got past was that unless the user is using Outlook,
the only file I could zip and mail was the open workbook using the
File.SendMail command. One of these days I'm going to write a macro to use
outlook but just haven't yet gottn to it.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Michael said:
Help, I have written a macro that performs calculations in
a spreadsheet and e-mails it to the relevant people. The
problem I have is that the file is large and would benefit
from compression, is there a way of writing this in to the
Macro.

Thanks
Michael
Click to expand...
Click to expand...
 
Ron,

I should have known you would have one by now<G>

Bob

Ron de Bruin said:
Hi Bob/Michael

See this example

http://www.rondebruin.nl/sendmail.htm#Zip

My Outlook Add-in have this option also
http://www.rondebruin.nl/sendmail-addin.htm

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Michael,

Here is something Ron de Bruin wrote on this subject

On the zipping part, you may want to check WinZip's web site. A few months
ago they introduced an addin to Winzip called the command line support addon
which lets you enter command switches with the Basic Winzip command. you
could then use some code like this:

Sub Zip_File()
Dim Zip_Path As String, Command_Text As String, My_Prog
Try_Drive
Zip_Path = File_Sh.[AH13]
' Delete the old zip file if it exists to avoid adding this file
Command_Text = Zip_Path & Path_Sep & "wzzip -yp spc.zip spc.xls"
My_Prog = Shell(Command_Text, vbMaximizedFocus)
End Sub

The one "gotcha" I never got past was that unless the user is using Outlook,
the only file I could zip and mail was the open workbook using the
File.SendMail command. One of these days I'm going to write a macro to use
outlook but just haven't yet gottn to it.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Michael said:
Help, I have written a macro that performs calculations in
a spreadsheet and e-mails it to the relevant people. The
problem I have is that the file is large and would benefit
from compression, is there a way of writing this in to the
Macro.

Thanks
Michael
Click to expand...
Click to expand...
Click to expand...
 
Back
Top