ZIP Excel File

Hi,
I save my excel sheet onto my desktop automatically using a code with a specific name + current date.
This works fine.
I then open up that xls file and extract data + import data to it etc..
Which works fine.

My question is, in the end how can i go about zipping that excel file to the same naming convention and same location where it is being opened from?

Thank You

What zip program do you have & which version of Excel are you using?

Excel 2003.
Winzip program

The full version of WinZip?

Yes full version.
WinZip Professional v11.1.7466 at home

in saying that, this will be used at work and i do not know which specific version at work, and won't find out until seven days away.

I save my excel sheet onto my desktop automatically using a code with a specific name + current date.
This works fine.
I then open up that xls file and extract data + import data to it etc..
Which works fine.

Is all done by a macro by the way, not manually

Look here

http://www.rondebruin.nl/zip.htm

cheers, i'll give that a go next week. thank you

I am getting the following pop-up box:
http://www.fileden.com/files/2007/8/...inzipPopup.JPG

when executing the following Zip code:

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103
Declare Function OpenProcess Lib "kernel32" _
        (ByVal dwDesiredAccess As Long, _
        ByVal bInheritHandle As Long, _
        ByVal dwProcessId As Long) As Long

 

Declare Function GetExitCodeProcess Lib "kernel32" _
        (ByVal hProcess As Long, _
        lpExitCode As Long) As Long
Sub Zip_ActiveWorkbook()


    Dim PathWinZip As String, FileNameZip As String, FileNameXls As String
    Dim ShellStr As String, strDate As String

 

    PathWinZip = "C:\Program files\Winzip\"
    'This will check if this is the path where WinZip is installed.
    If Dir(PathWinZip & "winzip32.exe") = "" Then
        MsgBox "Please find your copy of winzip32.exe and try again"
        Exit Sub
    End If

     ' Build the date/Time string
    'strDate = Format(Now, "dd-mm-yy h-mm-ss")
    
    ' Build the path and name for the zip file
    FileNameZip = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, _
                    Len(ActiveWorkbook.Name) - 4) & ".zip"

 

    ' Build the path and name for the xls file
    FileNameXls = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, _
                    Len(ActiveWorkbook.Name) - 4) & ".xls"

 

    ' Use SaveCopyAs to save the file with a Date/Time stamp
' ActiveWorkbook.SaveCopyAs FileName:=FileNameXls

 

    'Zip the file
    ShellStr = PathWinZip & "Winzip32 -min -a" _
             & " " & Chr(34) & FileNameZip & Chr(34) _
             & " " & Chr(34) & FileNameXls & Chr(34)

    ShellAndWait ShellStr, vbHide

 

    'Delete the file that you saved with SaveCopyAs
'   Kill FileNameXls

End Sub

Function ShellAndWait(ByVal PathName As String, Optional WindowState)
    Dim hProg As Long
    Dim hProcess As Long, ExitCode As Long
    'fill in the missing parameter and execute the program
    If IsMissing(WindowState) Then WindowState = 1
    hProg = Shell(PathName, WindowState)
    'hProg is a "process ID under Win32. To get the process handle:
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
    Do
        'populate Exitcode variable
        GetExitCodeProcess hProcess, ExitCode
        DoEvents
    Loop While ExitCode = STILL_ACTIVE
End Function

Any help?