Help needed for zip files macro

[avidlearner]

Hi all,

I'm new to this forum and need help with a zip file macro. Basically I need the macro to zip up a number of files separately. I have something like this:

Sub Zipfile1()
Dim source As String
Dim target As String

source = "F:\Macro test\LNSPY.xls"
target = "F:\Macro test\LNSPY.zip"

Shell "C:\Program Files\WinZip\WINZIP32.EXE -a -r " & target & " " & source
End Sub

However, when i run the macro, there's a error saying "No files were found for this operation - nothing to do. (F:\Macro.zip).

Can someone help by telling me what is missing in my macro? Also, can you show me how to create a loop so that after zipping file1 = LNSPY.xls, I can zip up 3 more other files with a different name.

Thanks in advance.

 

[Diablo II]

try like this

ZipPath = "c:\programme\winzip\winzip32.exe"
  Shell ZipPath & " -a " & target & source & ".zip " & _
    target & source & ".xls"
  Application.Wait Now + TimeSerial(0, 0, 2)

 

[Ivan F Moala]

Hi all,

I'm new to this forum and need help with a zip file macro. Basically I need the macro to zip up a number of files separately. I have something like this:

Sub Zipfile1()
Dim source As String
Dim target As String

source = "F:\Macro test\LNSPY.xls"
target = "F:\Macro test\LNSPY.zip"

Shell "C:\Program Files\WinZip\WINZIP32.EXE -a -r " & target & " " & source
End Sub

However, when i run the macro, there's a error saying "No files were found for this operation - nothing to do. (F:\Macro.zip).

Can someone help by telling me what is missing in my macro? Also, can you show me how to create a loop so that after zipping file1 = LNSPY.xls, I can zip up 3 more other files with a different name.

Thanks in advance.

Shell interprets spaces as command sepeartors, you need to enclose them in "

one rougth way

    If InStr(1, source, " ", vbTextCompare) <> 0 Then
        source = Chr$(34) & source & Chr$(34)
    End If

    If InStr(1, target, " ", vbTextCompare) <> 0 Then
        target = Chr$(34) & target & Chr$(34)
    End If

As far as your 2nd Q.
Build up an Array and loop through this replacing your source and target strings with the new strings.

 

[avidlearner]

Hi all,

Thanks for your reply!

Ivan,

I did this and it works!

Sub Zipfile1()
Dim source As String
Dim target As String
source = "F:\Macro test\LNSPY.xls"
target = "F:\Macro test\LNSPY.zip"
If InStr(1, source, " ", vbTextCompare) <> 0 Then
source = Chr$(34) & source & Chr$(34)
End If
If InStr(1, target, " ", vbTextCompare) <> 0 Then
target = Chr$(34) & target & Chr$(34)
End If
Shell "C:\Program Files\WinZip\WINZIP32.EXE -a -r " & target & " " & source
End Sub

But I don't really understand or know how to write a loop command. Can you show me how to create a loop so that i can zip for e.g first file - "F:\Macro test\LNSPY.xls" then "F:\Macro test\clean\split.xls"? The loop will zip up all the files i define.

Once again, thanks in advance