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

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.

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" wrote in message
...
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 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)
www.rondebruin.nl



"Bob Phillips" wrote in message ...
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" wrote in message
...
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

Ron,

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

Bob

"Ron de Bruin" wrote in message
...
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)
www.rondebruin.nl



"Bob Phillips" wrote in message
...
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" wrote in message
...
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

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


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